{"id":79,"date":"2016-03-22T07:53:40","date_gmt":"2016-03-22T07:53:40","guid":{"rendered":"https:\/\/sqltutorial.org\/?page_id=79"},"modified":"2025-01-23T06:05:24","modified_gmt":"2025-01-23T13:05:24","slug":"sql-distinct","status":"publish","type":"page","link":"https:\/\/www.sqltutorial.org\/sql-distinct\/","title":{"rendered":"SQL DISTINCT"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to use the SQL DISTINCT<\/code> operator to select distinct values from a table.<\/p>\n\n\n\n

Introduction to SQL DISTINCT operator #<\/a><\/h2>\n\n\n\n

To select the distinct values from a column of a table, you use the DISTINCT<\/code> operator in the SELECT<\/code> clause as follows:<\/p>\n\n\n

SELECT<\/span> DISTINCT<\/span>\n  column1\nFROM<\/span>\n  table_name;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
In this syntax, the SELECT<\/code> statement returns a result set that contains unique values from the column1<\/code> of the table.<\/p>\n\n\n\n
SQL allows you to use multiple columns with the DISTINCT operator:<\/p>\n\n\n
SELECT DISTINCT\n  column1,\n  column2\nFROM\n  table_name;<\/code><\/span><\/pre>\n\n\n
In this case, the SELECT<\/code> statement uses the combination of values from both column1<\/code> and column2<\/code> to evaluate the uniqueness of row and include distinct rows in the result set.<\/p>\n\n\n\n
Note that the DISTINCT<\/code> only select distinct values from a table. It doesn’t delete duplicate rows in the table.<\/p>\n\n\n\n
If you want to select two columns and remove duplicates in one column, you should use the GROUP BY<\/a><\/code> clause instead.<\/p>\n\n\n\n
SQL DISTINCT operator examples #<\/a><\/h2>\n\n\n\n
We’ll use the employees<\/code> table in the sample database<\/a> to demonstrate how the DISTINCT<\/code> operator works.<\/p>\n\n\n\n
\"SQL<\/figure>\n\n\n\n
Selecting distinct rows from one column example #<\/a><\/h3>\n\n\n\n
The following statement retrieves the data from the salary<\/code> column of the employees<\/code> table and sorts<\/a> them from high to low:<\/p>\n\n\n
SELECT<\/span>\n  salary\nFROM<\/span>\n  employees\nORDER<\/span> BY<\/span>\n  salary DESC<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
+----------+\n| salary   |\n+----------+\n| 24000.00 |\n| 17000.00 |\n| 17000.00 |\n| 14000.00 |\n| 13500.00 |\n| 13000.00 |\n| 12000.00 |\n| 12000.00 |\n| 11000.00 |\n| 10000.00 |\n|  9000.00 |\n|  9000.00 |\n...<\/code><\/span><\/pre>\n\n\n
The result set has some duplicates, for example, 17000<\/code>, 12000<\/code>, and 9000<\/code>.<\/p>\n\n\n\n
The following statement uses the DISTINCT<\/code> operator to select unique values from the salary<\/code> column of the employees<\/code> table:<\/p>\n\n\n
SELECT<\/span> DISTINCT<\/span>\n  salary\nFROM<\/span>\n  employees\nORDER<\/span> BY<\/span>\n  salary DESC<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
  salary\n----------\n 24000.00\n 17000.00\n 14000.00\n 13500.00\n 13000.00\n 12000.00\n 11000.00\n 10000.00\n  9000.00\n...<\/code><\/span>Code language:<\/span> plaintext<\/span> (<\/span>plaintext<\/span>)<\/span><\/small><\/pre>\n\n\n
The output shows the DISTINCT<\/code> operator returns the unique salary amounts.<\/p>\n\n\n\n
Selecting distinct rows on multiple columns example #<\/a><\/h3>\n\n\n\n
The following statement retrieves the job id and salary from the employees<\/code> table:<\/p>\n\n\n
SELECT<\/span>\n  job_id,\n  salary\nFROM<\/span>\n  employees\nORDER<\/span> BY<\/span>\n  job_id,\n  salary DESC<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
 job_id |  salary\n--------+----------\n      1 |  8300.00\n      2 | 12000.00\n      3 |  4400.00\n      4 | 24000.00\n      5 | 17000.00\n      5 | 17000.00\n      6 |  9000.00\n      6 |  8200.00\n      6 |  7800.00\n      6 |  7700.00\n      6 |  6900.00\n      7 | 12000.00\n...<\/code><\/span>Code language:<\/span> plaintext<\/span> (<\/span>plaintext<\/span>)<\/span><\/small><\/pre>\n\n\n
The result set has some duplicate rows e.g., job id 5<\/code> salary 17000<\/code>. It means that there are two employees with the same job id and salary.<\/p>\n\n\n\n
The following statement uses the DISTINCT<\/code> operator to remove the duplicate values in job id and salary:<\/p>\n\n\n
SELECT<\/span> DISTINCT<\/span>\n  job_id,\n  salary\nFROM<\/span>\n  employees\nORDER<\/span> BY<\/span>\n  job_id,\n  salary DESC<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
 job_id |  salary\n--------+----------\n      1 |  8300.00\n      2 | 12000.00\n      3 |  4400.00\n      4 | 24000.00\n      5 | 17000.00\n      6 |  9000.00\n      6 |  8200.00\n      6 |  7800.00\n...<\/code><\/span>Code language:<\/span> plaintext<\/span> (<\/span>plaintext<\/span>)<\/span><\/small><\/pre>\n\n\n
Note that you still see the duplicate values in the job_id<\/code> column because the DISTINCT<\/code> operator uses values from both job_id<\/code> and salary<\/code> to evaluate the duplicate, not just values in the job_id<\/code> column.<\/p>\n\n\n\n
SQL DISTINCT and NULL #<\/a><\/h2>\n\n\n\n
In the database world, NULL<\/code> is a special value that represents unknown or missing data.<\/p>\n\n\n\n
Unlike regular values, NULL<\/code> does not equal anything, even itself. For example, the following expression will result in NULL<\/code> which is unknown:<\/p>\n\n\n
SELECT\n  NULL<\/span> = NULL<\/span>;<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
Typically, the DISTINCT<\/code> operator treats all NULL<\/code> values the same. Therefore, the DISTINCT<\/code> operator keeps only one NULL<\/code> in the result set.<\/p>\n\n\n\n
Note that this behavior may be different between database products.<\/p>\n\n\n\n
For example, the following statement returns the distinct phone numbers of employees:<\/p>\n\n\n
SELECT<\/span> DISTINCT<\/span>\n  phone_number\nFROM<\/span>\n  employees\nORDER<\/span> BY<\/span>\n  phone_number DESC<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
 phone_number\n--------------\n NULL\n 650.501.2876\n 650.501.1876\n 650.124.1224\n 650.123.4234\n...<\/code><\/span>Code language:<\/span> plaintext<\/span> (<\/span>plaintext<\/span>)<\/span><\/small><\/pre>\n\n\n
Notice that the query returns only one NULL<\/code> in the result set.<\/p>\n\n\n\n
Summary #<\/a><\/h2>\n\n\n\n
Try it<\/a><\/p>\n\n\n
Try it<\/a><\/p>\n\n\n
Try it<\/a><\/p>\n\n\n
Try it<\/a><\/p>\n\n\n
Try it<\/a><\/strong><\/p>\n\n\n