SQL Server SELECT Examples


By:   |   Updated: 2021-04-12   |   Comments   |   Related: More > T-SQL


Problem

When storing data in different tables in a SQL database, at some point in time you will want to retrieve some of that data. This is where the SELECT statement of the SQL language comes in. This tutorial will teach you how you can use SELECT to read, aggregate and sort data from one or more database tables.

Solution

All queries in this SQL tutorial are written on the Adventure Works sample database. You can install it on your machine so you can execute the queries yourself to see the results. You can also take the example queries and swap out the table names and column names with names from your own database. Since we’ll only by using SQL SELECT statement to read data, there’s no risk in changing or deleting data. The examples in this tip use the AdventureWorks2017 database.

SQL SELECT Statement Examples

In its most simple form, the SELECT clause has the following SQL syntax for a Microsoft SQL Server database:

SELECT *
FROM <TableName>;

This SQL query will select all columns and all rows from the table. For example:

SELECT *
FROM [Person].[Person];

This query selects all data from the Person table in the Person schema.

simple select *

If we want only a subset of the columns, we can explicitly list each column separated by a comma, instead of using the asterisk symbol which includes all columns.

SELECT BusinessEntityID, FirstName, LastName, ModifiedDate
FROM [Person].[Person];
simple  select with columns specified

Syntax for Sorting the Results

Sometimes you want to display the rows in a different order than the order SQL Server returns the results in. You can do this using the SQL ORDER BY clause.

SELECT column1, column2, column3, …
FROM <tablename>
ORDER BY columnX ASC | DESC

You can sort on one or more columns and you can choose to sort either ascending (ASC) or descending (DESC). Let’s return rows with ascending modified date:

SELECT BusinessEntityID, FirstName, LastName, ModifiedDate
FROM [Person].[Person]
ORDER BY [ModifiedDate] ASC;
order by asc

Ascending is the default, so you don’t need to specify ASC explicitly.

Now with descending modified date:

SELECT BusinessEntityID, FirstName, LastName, ModifiedDate
FROM [Person].[Person]
ORDER BY [ModifiedDate] DESC;
order by desc

You can sort on multiple columns, so let’s sort on descending modified date first, then on ascending first name.

SELECT BusinessEntityID, FirstName, LastName, ModifiedDate
FROM [Person].[Person]
ORDER BY [ModifiedDate] DESC, FirstName;
order by on multiple columns

You can see for example how the third and fourth row of the result set (Carla and Margaret) have swapped.

Filtering data from a table

Sometimes you’ll want to limit the number of rows being returned by the query, especially if you have a table with a large number of rows. If you’re just interested in taking a look at the data, you can use the TOP clause. This will for example return the first 10 rows of the table:

SELECT TOP(10)
     [BusinessEntityID]
    ,[FirstName]
    ,[LastName]
    ,[ModifiedDate]
FROM [Person].[Person];
SELECT with TOP (number)

Instead of using a number, you can also use a percentage. This query returns the number of rows equal (or almost equal) to 15% of the total row number:

SELECT TOP(15) PERCENT
     [BusinessEntityID]
    ,[FirstName]
    ,[LastName]
    ,[ModifiedDate]
FROM [Person].[Person];

In the case of this table (with a total of 19,972 rows), the query returns 2,996 rows.

top percent example

If you want to filter rows using a condition, the WHERE clause can be used. It generally has the following structure:

SELECT column1, column2, …
FROM <TableName>
WHERE <Boolean expression>;

Every row where the expression returns true is returned by the query. The following query returns all persons with the name "Rob".

SELECT
     [BusinessEntityID]
    ,[FirstName]
    ,[LastName]
    ,[ModifiedDate]
FROM [Person].[Person]
WHERE [FirstName] = 'Rob';
simple where clause example

The following example three queries return rows where BusinessEntityID is equal, smaller or bigger than 130.

SELECT
     [BusinessEntityID]
    ,[FirstName]
    ,[LastName]
    ,[ModifiedDate]
FROM [Person].[Person]
WHERE [BusinessEntityID] = 130;
businessentity equals 130
SELECT
     [BusinessEntityID]
    ,[FirstName]
    ,[LastName]
    ,[ModifiedDate]
FROM [Person].[Person]
WHERE [BusinessEntityID] < 130;
businessentity < 130
SELECT
     [BusinessEntityID]
    ,[FirstName]
    ,[LastName]
    ,[ModifiedDate]
FROM [Person].[Person]
WHERE [BusinessEntityID] > 130;
result set

You can use different functions and operators, such as the LIKE operator for more advanced string searching. In this example, we search all person where the name starts with "Rob".

SELECT
     [BusinessEntityID]
    ,[FirstName]
    ,[LastName]
    ,[ModifiedDate]
FROM [Person].[Person]
WHERE [FirstName] LIKE 'Rob%';
where clause with LIKE

For more LIKE examples, check out the tip SQL Server LIKE Syntax with Wildcard Characters. An example using the YEAR function:

SELECT
     [BusinessEntityID]
    ,[FirstName]
    ,[LastName]
    ,[ModifiedDate]
FROM [Person].[Person]
WHERE YEAR([ModifiedDate]) = 2011;

Only rows where the row was modified in the year 2011 are now returned:

where  with year function

You can use the Boolean operators AND, OR and NOT to combine different Boolean expressions with each other. The following query selects all rows where the firstname equals "Rob" and the modified date was in the year 2011:

SELECT
     [BusinessEntityID]
    ,[FirstName]
    ,[LastName]
    ,[ModifiedDate]
FROM [Person].[Person]
WHERE  [FirstName]           = 'Rob'
    AND YEAR([ModifiedDate]) = 2011;
where with AND

We get a whole different result set when we replace the AND by OR. Now all rows are returned where the first name equals to Rob or where the row was modified in 2011.

SELECT
     [BusinessEntityID]
    ,[FirstName]
    ,[LastName]
    ,[ModifiedDate]
FROM [Person].[Person]
WHERE  [FirstName]          = 'Rob'
    OR YEAR([ModifiedDate]) = 2011;
where with OR

Grouping data from a SQL Server table

If you don’t want to return individual detail rows from a table, but rather aggregated results, you can use the GROUP BY clause. The query takes the following format:

SELECT column1, column2, <aggregationfunction>(column3) 
FROM <TableName>
GROUP BY column1, column2

There are many different aggregation functions in SQL Server, such as SUM, AVG, MIN, MAX and so on. You can find a list here. If you only use aggregations, you don’t need to use the GROUP BY clause. For example, we can return the number of rows in a table with the COUNT function:

SELECT COUNT(1) AS RowCnt
FROM [Person].[Person];
row count

A new name – RowCnt – has been assigned to the result using the AS keyword. This is also called "assigning an alias". By adding a GROUP BY on the first name and a WHERE clause (see the previous section), we can count the number of times a name starts with "Rob".

SELECT
     [FirstName]
    ,COUNT(1) AS RowCnt
FROM [Person].[Person]
WHERE [FirstName] LIKE 'Rob%'
GROUP BY [FirstName];
group by with aggregate and column

Every column that is not used inside an aggregation function and that is not a constant, should be put in the GROUP BY clause. For example, if we add LastName to the SELECT but not to the GROUP BY, we get an error:

group by error

Filtering Aggregated Results

Using the WHERE clause you can filter out individual rows. But what if you want to filter on the result of an aggregated function? This is not possible in the WHERE clause, since those results don’t exist in the original table. We can do this with the HAVING clause. Using the previous example, we want to return only first names – starting with "Rob" – where the row count is at least 20. The query becomes:

SELECT
     [FirstName]
    ,COUNT(1) AS RowCnt
FROM [Person].[Person]
WHERE [FirstName] LIKE 'Rob%'
GROUP BY [FirstName]
HAVING COUNT(1) >= 20;
group by with having

Selecting data from multiple SQL Server tables

Often you don’t need data from one single table, but you’ll need to combine different tables to get the result you want. In SQL, you do this by "joining" tables. You take one table, and you define which columns need to match with columns of another table. There are different join types in SQL:

  • INNER JOIN – only rows matching between both tables are returned.
  • LEFT OUTER JOIN – all rows from the first table are returned, along with any matching rows from the second table. There’s also a RIGHT JOIN, which reverses the relationship.
  • FULL OUTER JOIN – this returns all rows from both tables. If there’s no match, the missing side will have NULL values instead of actual column values.
  • CROSS JOIN – this is the cartesian product of all rows of both tables. There’s no matching. If you have 100 rows in the first table and 10 in the second, the result set will contain 100 * 10 = 1000 rows. This join type should be used carefully because it can potentially return a lot of rows. We won’t discuss it further in this tip.

The blog post A Visual Explanation of SQL Joins illustrates each join type with a Venn diagram. Let’s illustrate each join type with a query example.

INNER JOIN

The following query uses INNER JOIN to return all rows and all columns of the Person table and the Employee table, but only if they have matching BusinessEntityIDs. In other words, the query returns the persons who are employees of AdventureWorks.

SELECT *
FROM       [Person].[Person]
INNER JOIN [HumanResources].[Employee] ON [Employee].[BusinessEntityID] = [Person].[BusinessEntityID];
inner join

If you don’t want to type the table names each time you reference a column, you can alias those as well. Here the Person table is aliased with "p" and the Employee table with "e".

SELECT *
FROM       [Person].[Person]            p
INNER JOIN [HumanResources].[Employee]  e ON e.[BusinessEntityID] = p.[BusinessEntityID];

When you have columns with the same name between the tables, you need to prefix them with the table name or with the respective alias. In the following query, we return the BusinessEntityID of both tables, so they need to be prefixed.

SELECT 
     p.[FirstName]
    ,p.[LastName]
    ,p.[BusinessEntityID]
    ,e.[BusinessEntityID]
    ,e.[HireDate]
FROM       [Person].[Person]            p
INNER JOIN [HumanResources].[Employee]  e ON e.[BusinessEntityID] = p.[BusinessEntityID];
returning columns from both tables

It’s a best practice to prefix al the columns in the SELECT statement with the table alias, as it will make clear of which table each column comes from. This avoids any confusion by people reading your query, especially if they’re not familiar with the database design.

LEFT OUTER JOIN

When we change the join type to a LEFT OUTER JOIN, all rows from the Person table will be returned. It doesn’t matter if there’s a match with the Employee table. If there’s a match, the value of the HireDate column will be returned. If not, NULL will be returned instead.

SELECT 
     p.[FirstName]
    ,p.[LastName]
    ,p.[BusinessEntityID]
    ,e.[BusinessEntityID]
    ,e.[HireDate]
FROM            [Person].[Person]           p
LEFT OUTER JOIN [HumanResources].[Employee] e ON e.[BusinessEntityID] = p.[BusinessEntityID];

In the result set, you can see that there’s no HireDate returned for the persons who are not an employee:

example with LEFT JOIN

RIGHT OUTER JOIN uses the exact same principle, but it will return all rows from the second table and only matching results from the first table. It typically isn’t used much, as LEFT OUTER JOIN is easier to read.

FULL OUTER JOIN

To illustrate the concept of FULL OUTER JOIN, we are involving the JobCandidate table as well. This table contains 13 rows. Only 2 of those 13 candidates have actually been hired, and those have a BusinessEntityID that is not NULL.

result set

In the following query, we are first joining Person and Employee together, to find all Employees. Then we are using FULL OUTER JOIN to get all job candidates in the query result set as well.

SELECT 
     p.[FirstName]
    ,p.[LastName]
    ,p.[BusinessEntityID]
    ,e.[BusinessEntityID]
    ,j.[BusinessEntityID]
    ,e.[HireDate]
    ,j.[JobCandidateID]
FROM            [Person].[Person]               p
INNER JOIN      [HumanResources].[Employee]     e ON p.[BusinessEntityID] = e.[BusinessEntityID]
FULL OUTER JOIN [HumanResources].[JobCandidate] j ON e.[BusinessEntityID] = j.[BusinessEntityID];

There are 290 employees and 13 job candidates. Two job candidates have been hired, so they’re employees as well. This means the grand total of rows returned should be 301 (290 + 13 – 2).

The first rows are employees who cannot be found in the JobCandidate table. The JobCandidateID column is NULL.

result set

At the end of the result set, the job candidates are added:

result set

In the red square, we have the 11 job candidates who have not been hired. All columns are NULL except the JobCandidateID column. In the green square, we have an example of a job candidate which has been hired. All columns have values in this case.

The tip SQL Server Join Example goes a bit more into detail about the different join types.

Conclusion

Let’s wrap all the previous sections together into one single query. This query returns the count of all employees which name starts with "Rob", sorted on this count ascending.

SELECT 
     p.[FirstName]
    ,COUNT(1) AS RowCnt
FROM       [Person].[Person]           p
INNER JOIN [HumanResources].[Employee] e ON e.[BusinessEntityID] = p.[BusinessEntityID]
WHERE p.[FirstName] LIKE 'Rob%'
GROUP BY [p].[FirstName]
ORDER BY [RowCnt] ASC;
more complex example

As the query shows, you can also sort on aliases (RowCnt in this example) and not only on actual physical columns from a table.

Next Steps


Last Updated: 2021-04-12


get scripts

next tip button



About the author
MSSQLTips author Koen Verbeeck Koen Verbeeck is a BI professional, specializing in the Microsoft BI stack with a particular love for SSIS.

View all my tips



Comments For This Article





download





Recommended Reading

SQL Server Cursor Example

Using MERGE in SQL Server to insert, update and delete at the same time

Rolling up multiple rows into a single row and column for SQL Server data

How to use @@ROWCOUNT in SQL Server

SQL Server Loop through Table Rows without Cursor














get free sql tips
agree to terms