{"id":176,"date":"2018-04-13T11:39:50","date_gmt":"2018-04-13T04:39:50","guid":{"rendered":"http:\/\/www.sqlservertutorial.net\/?page_id=176"},"modified":"2024-09-24T21:05:31","modified_gmt":"2024-09-24T14:05:31","slug":"sql-server-select-distinct","status":"publish","type":"page","link":"https:\/\/www.sqlservertutorial.net\/sql-server-basics\/sql-server-select-distinct\/","title":{"rendered":"SQL Server SELECT DISTINCT"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to use the SQL Server SELECT DISTINCT<\/code> clause to return distinct rows from a result set.<\/p>\n\n\n\n

Introduction to SQL Server SELECT DISTINCT clause #<\/a><\/h2>\n\n\n\n

Sometimes, you may want to get only distinct values in a specified column of a table. To achieve this, you can use the SELECT DISTINCT<\/code> clause. <\/p>\n\n\n\n

Here’s how you can do it:<\/p>\n\n\n

SELECT<\/span> \n  DISTINCT<\/span> column_name \nFROM<\/span> \n  table_name;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The query will return unique values from the column column_name<\/code>. In other words, it removes duplicate values from the column_name<\/code> in the result set.<\/p>\n\n\n\n
If you use the DISTINCT<\/code> clause with multiple columns as follows:<\/p>\n\n\n
SELECT<\/span> DISTINCT<\/span>\n\tcolumn_name1,\n\tcolumn_name2 ,\n\t...\nFROM<\/span>\n\ttable_name;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The query will evaluate the uniqueness based on the combination of values in all the specified columns in the SELECT<\/code> list. It will return only rows with unique combinations of the specified columns.<\/p>\n\n\n\n
When you apply the DISTINCT<\/code> clause to a column that contains NULLs, it will keep only one NULL and eliminate the others. In other words, the DISTINCT<\/code> clause treats all NULLs as the same value.<\/p>\n\n\n\n
SQL Server SELECT DISTINCT examples #<\/a><\/h2>\n\n\n\n
We will use the customers<\/code> table from the sample database<\/a> for the demonstration:<\/p>\n\n\n\n
\"\"\/<\/figure>\n\n\n\n
1) Using the SELECT DISTINCT with one column #<\/a><\/h3>\n\n\n\n
The following statement uses the SELECT<\/a> statement to retrieve all cities of all customers from the customers<\/code> tables:<\/p>\n\n\n
SELECT<\/span> \n  city \nFROM<\/span> \n  sales.customers \nORDER<\/span> BY<\/span> \n  city;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"SQL<\/figure>\n\n\n\n
The output indicates that the cities are duplicates.<\/p>\n\n\n\n
To retrieve only distinct cities, you can use the SELECT DISTINCT<\/code> keyword as follows:<\/p>\n\n\n
SELECT<\/span> \n  DISTINCT<\/span> city \nFROM<\/span> \n  sales.customers \nORDER<\/span> BY<\/span> \n  city;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"SQL<\/figure>\n\n\n\n
The output shows that the SELECT DISTINCT<\/code> returns only distinct cities without duplicates.<\/p>\n\n\n\n
2) Using SELECT DISTINCT with multiple columns #<\/a><\/h3>\n\n\n\n
The following example uses the SELECT<\/a> statement to retrieve the cities and states of all customers from the customers<\/code> table:<\/p>\n\n\n
SELECT<\/span> \n  city, \n  state \nFROM<\/span> \n  sales.customers \nORDER<\/span> BY<\/span> \n  city, \n  state;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"SQL<\/figure>\n\n\n\n
The output indicates that there are duplicate cities & states, for example, Albany NY<\/code>, Amarillo TX<\/code>, and so on.<\/p>\n\n\n\n
To retrieve the distinct cities and states of customers, you can use the SELECT DISTINCT<\/code> with the city<\/code> and state<\/code> columns:<\/p>\n\n\n
SELECT<\/span> \n  DISTINCT<\/span> city, state \nFROM<\/span> \n  sales.customers<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"SQL<\/figure>\n\n\n\n
In this example, the statement uses the combination of values in both city<\/code> and state<\/code> columns to evaluate the duplicate.<\/p>\n\n\n\n
3) Using SELECT DISTINCT with NULL #<\/a><\/h3>\n\n\n\n
The following statement finds the distinct phone numbers of customers:<\/p>\n\n\n
SELECT<\/span> \n  DISTINCT<\/span> phone \nFROM<\/span> \n  sales.customers \nORDER<\/span> BY<\/span> \n  phone;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"SQL<\/figure>\n\n\n\n
In this example, the DISTINCT<\/code> clause keeps only one NULL in the phone<\/code> column and removes other NULLs.<\/p>\n\n\n\n
DISTINCT vs. GROUP BY #<\/a><\/h2>\n\n\n\n
The following statement uses the GROUP BY<\/code> clause to return distinct cities together with state and zip code from the sales.customers<\/code> table:<\/p>\n\n\n
SELECT<\/span> \n  city, \n  state, \n  zip_code \nFROM<\/span> \n  sales.customers \nGROUP<\/span> BY<\/span> \n  city, \n  state, \n  zip_code \nORDER<\/span> BY<\/span> \n  city, \n  state, \n  zip_code<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The following picture shows the partial output:<\/p>\n\n\n\n
\"SQL<\/figure>\n\n\n\n
It is equivalent to the following query that uses the DISTINCT<\/code> operator :<\/p>\n\n\n
SELECT<\/span> \n  DISTINCT<\/span> city, state, zip_code \nFROM<\/span> \n  sales.customers;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Both DISTINCT<\/code> and GROUP BY<\/code> clause reduces the number of returned rows in the result set by removing the duplicates.<\/p>\n\n\n\n
Summary #<\/a><\/h2>\n\n\n\n
    \n
  • Use the SQL Server SELECT DISTINCT<\/code> clause to retrieve the distinct values from one or more columns.<\/li>\n<\/ul>\n
    \n\t
    \n\t\t
    Was this tutorial helpful?<\/div>\n\t\t
    \n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
    \n\t\t
    \n\t\t\t
    <\/div>\n\t\t\t\n\t\t\t
However, you should use the GROUP BY<\/code> clause when you want to apply an aggregate function<\/a> to one or more columns.<\/p>\n\n\n\n