SELECT Statement in SQL Server

For example, we will see how to select specific rows or all columns, selecting distinct rows, filtering with where clause, sorting rows using orderby and so on.

We will be using the AdventureWorks2012 database for this demo.

1. To select all the rows and columns from a table, we use the following query:

SELECT * FROM HumanResources.Employee

Execute the query by pressing F5 or via the execute button.

Output



In the output, we get all the rows and columns.

There is another way to select all the columns from a table. Instead of using * we can specify the column names.

SELECT  BusinessEntityID,NationalIDNumber,LoginID,OrganizationNode,OrganizationLevel,JobTitle,BirthDate,MaritalStatus,Gender,HireDate,SalariedFlag,VacationHours,SickLeaveHours,CurrentFlag,rowguid,ModifiedDate FROM HumanResources.Employee

The output will be the same.

If you feel lazy in writing this long query given above then what you can do is go to the Object Explorer window then expand adventureWorks2012 then select HumanResources.Employee table then right-click on it then "select script table as" then select "To" then you will see a New query editor window.



SQL Server will generate the SELECT query for us.



Execute it. The output will be the same.

Note

For performance reasons, it is better to specify the column names instead of using *.

2. Selecting distinct rows

In the HumanResources.Employee there are duplicate organizational levels. Let’s say we want all the available OrganizationLevels in this table and for that we can use the DISTINCT keyword.

SELECT DISTINCT OrganizationLevel FROM HumanResources.Employee;

Execute it



So, there are four OrganizationLevels in this table.

What if you want the distinct rows from more than one column?

SELECT DISTINCT JobTitle,OrganizationLevel FROM HumanResources.Employee;

Execute it.



In the output we can see that the OrganizationalLevel is not distinct anymore. But when we use the DISTINCT keyword on more than one column, we are actually saying to the SQL Server that the value should be distinct across these two columns. This is why we got the above output.

3. Filtering the values using where clause.

Let’s say I want the loginID of all the employees whose gender is F (female). For that we can use the where clause.

SELECT LoginID FROM HumanResources.Employee
WHERE Gender = 'F'

After FROM we specify the WHERE clause.



What if you want to display the LoginID of all the employees whose gender is not F?

The first way is to pass M instead of F.

SELECT LoginID FROM HumanResources.Employee WHERE Gender = 'M'

The other way is to say WHERE Gender is not equal to (<>) or (!=) F.

SELECT LoginID FROM HumanResources.Employee WHERE Gender <> 'F'

In the output, we get all the male loginIDs.

Until now we have seen two operators but there are more than that.

• = -> equal to
• > -> Greater than
• >= -> Greater than equal to
• < -> less than
• <= -> less than equal to
• IN -> specify a list of values
• BETWEEN -> specify a range
• LIKE -> specify a pattern
• NOT -> not in a list or range
  

Using those operators, we have some wild cards too.

• % -> specifies zero or more characters
• _ -> specify exactly one character
• [] -> any character with in the brackets
• [^] -> not any character with in the brackets
  

4. IN operator

To specify a list of values, we use the IN operator.

Let’s say we want to select the employees whose OrganizationLevel is 1, 2, 3, or 4.

For this kind of situation we will have multiple OrganizationalLevel conditions.

SELECT * FROM HumanResources.Employee WHERE OrganizationLevel = 1 OR OrganizationLevel = 2 OR OrganizationLevel = 3 OR OrganizationLevel = 4

If I want to get the records of all the employees whose OrganizationLevel is 1, 2, 3 or 4 then we need to use the preceding query. But there are many conditions. Instead of adding multiple OR conditions we can use the IN operator.

SELECT * FROM HumanResources.Employee WHERE OrganizationLevel IN(1,2,3,4)

The output will be the same but the query looks clean and more manageable.



5. BETWEEN operator

To specify a range of values, we use the BETWEEN operator.

If you want all the employee records with VacationHours between 40 and 60 hours, we can use the BETWEEN operator.

SELECT * FROM HumanResources.Employee WHERE VacationHours BETWEEN 40 AND 60

Note

The boundary conditions 40 and 60 are inclusive.

6. LIKE operator

One of the most interesting and very useful operators in SQL Server is the LIKE operator. If you want to retrieve records based on some pattern, use the LIKE operator.

Let’s say I want all the NationalIDNumbers that start with 1.

SELECT NationalIDNumber FROM HumanResources.Employee WHERE NationalIDNumber LIKE '1%'

1% means the value must have 1 in the beginning.



If you reverse the pattern from 1% to %1 then:

SELECT NationalIDNumber FROM HumanResources.Employee WHERE NationalIDNumber LIKE '%1'

You will get the records whose NationalIDNumber ends with value 1.



LIKE operator with [] wildcard

The [] wildcard means any matching character.

--LIKE operator with [] wildcard
SELECT NationalIDNumber FROM HumanResources.Employee WHERE NationalIDNumber LIKE '[1,2]%'

'[1,2]%' states any matching character in the beginning.

--LIKE operator with NOT operator and the []% wildcard
SELECT NationalIDNumber FROM HumanResources.Employee WHERE NationalIDNumber NOT LIKE '[1,2]%'

NOT LIKE '[1,2]%' this condition means that we want all the NationalIDNumber that does not start with 1 or 2.



To get the same thing, we can use [^] wildcard.

SELECT NationalIDNumber FROM HumanResources.Employee WHERE NationalIDNumber LIKE '[^1,2]%'

The output will be the same.

7. Joining multiple conditions using AND and OR operators

Let’s say I want to select and retrieve the employee records with vacation hour of 40 or 60 and whose gender is F.

SELECT * FROM HumanResources.Employee WHERE (VacationHours = 40 OR VacationHours = 80) AND Gender = 'F'

8. Sorting rows using OrderBy

Let’s say we want the LoginID and the vacation hours of all employees whose gender is F but I want the vacation hours to be ordered in descending order. For that we can use the orderby clause.

SELECT LoginID, VacationHours FROM HumanResources.Employee WHERE Gender = 'M' ORDER BY VacationHours DESC

Note

By default ORDERBY displays the result in ascending order.



9. Selecting Top n rows

Let’s say we want the LoginID and VacationHours of all the employees with gender of M. We want the VacationHours in DESC order and out of all the matching records we want the first top record.

SELECT TOP 1 LoginID, VacationHours FROM HumanResources.Employee WHERE Gender = 'M' ORDER BY VacationHours DESC

In the next article, we will see how the GROUPBY clause works. Until then keep learning.

Thank you.