SELECT DISTINCT clause in sql server

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:
The query returns only distinct values in the specified column. In other words, it removes the duplicate values in the column from the result set.
If you use multiple columns as follows:
The query uses 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 eliminates the other. In other words, the DISTINCT clause treats all NULL “values” as the same value.

SQL Server SELECT DISTINCT examples

For the demonstration, we will use the customers table from the sample database.

A) DISTINCT one column example

The following statement returns all cities of all customers in the customers tables:
SQL Server SELECT DISTINCT - duplicate cities
As you can see clearly from the output, the cities are duplicate.
To get distinct cities, you add the DISTINCT keyword as follows:
SQL Server SELECT DISTINCT - distinct cities
Now, the query returns a distinct value for each group of duplicates. In other words, it removed all duplicate cities from the result set.

B) DISTINCT multiple columns example

This statement returns all cities and states of all customers:
SQL Server SELECT DISTINCT - multiple columns example before
The following statement finds the distinct city and state of all customers.
SQL Server SELECT DISTINCT - multiple columns example
In this example, the statement used the combination of values in both  city and state columns to evaluate the duplicate.

C) DISTINCT with null values example

The following example finds the distinct phone numbers of the customers:
SQL Server SELECT DISTINCT - null example
In this example, the DISTINCT clause kept only one NULL in the phone column and removed the 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:
The following picture shows the partial output:
It is equivalent to the following query that uses the DISTINCT operator :
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 on one or more columns.
In this tutorial, you have learned how to use the SQL Server SELECT DISTINCT clause to retrieve the distinct values in a specified list of columns.

Post a Comment

0 Comments