SELECT Statement in SQL Server

SELECT statement in SQL is one of the most used SQL queries. SELECT query gets the matched rows from a database table or multiple tables and their selected columns specified in the query. The following is a syntax of a SELECT query. 

[ WITH { [ XMLNAMESPACES ,] [ <common_table_expression> ] } ]
SELECT select_list [ INTO new_table ]
[ FROM table_source ] [ WHERE search_condition ]
[ GROUP BY group_by_expression ]
[ WINDOW window expression]
[ HAVING search_condition ]
[ ORDER BY order_expression [ ASC | DESC ] ]

The UNION, EXCEPT, and INTERSECT operators can be used between queries to combine or compare their results into one result set.

In this post, we will see how to use a SQL SELECT statement with various options. Some examples are how to select specific rows or all columns, selecting distinct rows, filtering with where clause, sorting rows using orderby and so on.

Note: We will be using the AdventureWorks2012 database for this demo.

1. How to select all rows from a table in SQL

The following SELECT query gets all rows and columns from a database table:

SELECT * FROM HumanResources.Employee 

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

The output looks like the following: 

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 in your SELECT query instead of using *.

2. Selecting distinct rows of a table using SQL

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. The output looks like the following:

So, there are four OrganizationLevels in this table.

What if you want distinct rows from more than one column? Here is the query for it.

SELECT DISTINCT JobTitle,OrganizationLevel FROM HumanResources.Employee; 

Execute it and you will see the results.

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 values using WHERE clause in SELECT 

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. The WHERE clause has one or more conditions or expressions. 

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. We can use a full expression with multiple conditions and even nested conditons in a WHERE clause.

• = -> 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. Using IN operator in SELECT

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. Using BETWEEN operator in SELECT

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. Using LIKE operator in SELECT

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 from a table

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 

Summary

In this article, we learned basics of the SELECT statement in SQL and how to use a SELECT statement with its various combinations in SQL Server.

In the next article, we will see how the GROUPBY clause works. Until then keep learning. If you're new to SQL and SQL Server, start with What is SQL.

Thank you.