In this SQL Server tutorial, you’ll learn how to use SELECT DISTINCT clause in SQL Server to return distinct values. We will also learn its syntax along with some examples.
The DISTINCT clause in SQL Server is mainly used to remove duplicate values and return distinct values from the result set. You can use the DISTINCT clause only with the SELECT statements in SQL Server.
SELECT DISTINCT expressions FROM tables [WHERE conditions];
- These are the columns or calculations that you wish to return
- These are one or more tables that you wish to get the records from. You will need to have atleast one table specified in the FROM clause of the SELECT query.
- This is an optional parameter used mainly to filter out the records.
1. Using DISTINCT clause with one column
The below statement returns all the FirstNames of the Person table.
SELECT FirstName FROM [Person].[Person]
You will notice that there are some first names that are duplicate. You can get the unique or distinct first names by adding the DISTINCT keyword to the SELECT clause as shown below.
SELECT DISTINCT FirstName FROM [Person].[Person]
Now, the above query when run will remove all the duplicate first names from the result set and return you the unique or distinct set of first names.
2.Using DISTINCT clause with multiple columns.
The below query demonstrates the usage of DISTINCT clause used with multiple columns.
SELECT DISTINCT FirstName, LastName FROM [Person].[Person]
The above query used the combination of both firstname and last name to evaluate duplicate values and return the unique or distinct values.
Note: When using the SELECT DISTINCT clause, you will notice that the DISTINCT clause will not ignore the NULL values. Your result set will include the NULL values as distinct values when it finds one.