SQL Server SELECT DISTINCT

Summary: in this tutorial, you will learn how to use the SQL Server SELECT DISTINCT clause to retrieve the only distinct values in a specified list of columns.

Introduction to SQL Server SELECT DISTINCT clause

Sometimes, you may want to get only distinct values in a specified column of a table. To do this, you use the SELECT DISTINCT clause as follows:

SELECT 
  DISTINCT column_name 
FROM 
  table_name;Code language: SQL (Structured Query Language) (sql)

The query returns only distinct values in the specified column. In other words, it removes the duplicate values from the column in the result set.

If you use the DISTINCT with multiple columns as follows:

SELECT DISTINCT
	column_name1,
	column_name2 ,
	...
FROM
	table_name;Code language: SQL (Structured Query Language) (sql)

The query will use the combination of values in all specified columns in the SELECT list to evaluate the uniqueness.

If you apply the DISTINCT clause to a column that has NULL, the DISTINCT clause will keep only one NULL and eliminate the others. In other words, the DISTINCT clause treats all NULL “values” as the same value.

SQL Server SELECT DISTINCT examples

We will use the customers table from the sample database for the demonstration:

1) Using the SELECT DISTINCT with one column

The following statement uses the SELECT statement to retrieve all cities of all customers from the customers tables:

SELECT 
  city 
FROM 
  sales.customers 
ORDER BY 
  city;Code language: SQL (Structured Query Language) (sql)

The output indicates that the cities are duplicates.

To retrieve only distinct cities, you can use the SELECT DISTINCT keyword as follows:

SELECT 
  DISTINCT city 
FROM 
  sales.customers 
ORDER BY 
  city;Code language: SQL (Structured Query Language) (sql)

The output shows that the SELECT DISTINCT returns only distinct cities without duplicates.

2) Using SELECT DISTINCT with multiple columns

The following example uses the SELECT statement to retrieve the cities and states of all customers from the customers table:

SELECT 
  city, 
  state 
FROM 
  sales.customers 
ORDER BY 
  city, 
  state;Code language: SQL (Structured Query Language) (sql)

The output indicates that there are duplicate cities & states, for example, Albany NY, Amarillo TX, and so on.

To retrieve the distinct cities and states of customers, you can use the SELECT DISTINCT with the city and state columns:

SELECT 
  DISTINCT city, state 
FROM 
  sales.customersCode language: SQL (Structured Query Language) (sql)

In this example, the statement uses the combination of values in both city and state columns to evaluate the duplicate.

3) Using SELECT DISTINCT with NULL

The following statement finds the distinct phone numbers of customers:

SELECT 
  DISTINCT phone 
FROM 
  sales.customers 
ORDER BY 
  phone;Code language: SQL (Structured Query Language) (sql)

In this example, the DISTINCT clause keeps only one NULL in the phone column and removes other NULLs.

DISTINCT vs. GROUP BY

The following statement uses the GROUP BY clause to return distinct cities together with state and zip code from the sales.customers table:

SELECT 
  city, 
  state, 
  zip_code 
FROM 
  sales.customers 
GROUP BY 
  city, 
  state, 
  zip_code 
ORDER BY 
  city, 
  state, 
  zip_codeCode language: SQL (Structured Query Language) (sql)

The following picture shows the partial output:

It is equivalent to the following query that uses the DISTINCT operator :

SELECT 
  DISTINCT city, state, zip_code 
FROM 
  sales.customers;Code language: SQL (Structured Query Language) (sql)

Both DISTINCT and GROUP BY clause reduces the number of returned rows in the result set by removing the duplicates.

However, you should use the GROUP BY clause when you want to apply an aggregate function to one or more columns.

Summary

• Use the SQL Server SELECT DISTINCT clause to retrieve the distinct values from one or more columns.