SQL DISTINCT

Summary: in this tutorial, you will learn how to use the SQL DISTINCT operator to select distinct values from a table.

Introduction to SQL DISTINCT operator 

To select the distinct values from a column of a table, you use the DISTINCT operator in the SELECT clause as follows:

SELECT DISTINCT
  column1
FROM
  table_name;

In this syntax, the SELECT statement returns a result set that contains unique values from the column1 of the table.

SQL allows you to use multiple columns with the DISTINCT operator:

SELECT DISTINCT
  column1,
  column2
FROM
  table_name;

In this case, the SELECT statement uses the combination of values from both column1 and column2 to evaluate the uniqueness of row and include distinct rows in the result set.

Note that the DISTINCT only select distinct values from a table. It doesn’t delete duplicate rows in the table.

If you want to select two columns and remove duplicates in one column, you should use the GROUP BY clause instead.

SQL DISTINCT operator examples 

We’ll use the employees table in the sample database to demonstrate how the DISTINCT operator works.

Selecting distinct rows from one column example 

The following statement retrieves the data from the salary column of the employees table and sorts them from high to low:

SELECT
  salary
FROM
  employees
ORDER BY
  salary DESC;

Try it here.

+----------+
| salary |
+----------+
| 24000.00 |
| 17000.00 |
| 17000.00 |
| 14000.00 |
| 13500.00 |
| 13000.00 |
| 12000.00 |
| 12000.00 |
| 11000.00 |
| 10000.00 |
| 9000.00 |
| 9000.00 |

The result set has some duplicates, for example, 17000, 12000, and 9000.

The following statement uses the DISTINCT operator to select unique values from the salary column of the employees table:

SELECT DISTINCT
  salary
FROM
  employees
ORDER BY
  salary DESC;

Try it here.

  salary
----------
 24000.00
 17000.00
 14000.00
 13500.00
 13000.00
 12000.00
 11000.00
 10000.00
  9000.00

The output shows the DISTINCT operator returns the unique salary amounts.

Selecting distinct rows on multiple columns example 

The following statement retrieves the job id and salary from the employees table:

SELECT
  job_id,
  salary
FROM
  employees
ORDER BY
  job_id,
  salary DESC;

Try it here.

 job_id |  salary
--------+----------
      1 |  8300.00
      2 | 12000.00
      3 |  4400.00
      4 | 24000.00
      5 | 17000.00
      5 | 17000.00
      6 |  9000.00
      6 |  8200.00
      6 |  7800.00
      6 |  7700.00
      6 |  6900.00
      7 | 12000.00

The result set has some duplicate rows e.g., job id 5 salary 17000. It means that there are two employees with the same job id and salary.

The following statement uses the DISTINCT operator to remove the duplicate values in job id and salary:

SELECT DISTINCT
  job_id,
  salary
FROM
  employees
ORDER BY
  job_id,
  salary DESC;

Try it here.

 job_id |  salary
--------+----------
      1 |  8300.00
      2 | 12000.00
      3 |  4400.00
      4 | 24000.00
      5 | 17000.00
      6 |  9000.00
      6 |  8200.00
      6 |  7800.00

Note that you still see the duplicate values in the job_id column because the DISTINCT operator uses values from both job_id and salary to evaluate the duplicate, not just values in the job_id column.

SQL DISTINCT and NULL 

In the database world, NULL is a special value that represents unknown or missing data.

Unlike regular values, NULL does not equal anything, even itself. For example, the following expression will result in NULL which is unknown:

SELECT
  NULL = NULL;

Typically, the DISTINCT operator treats all NULL values the same. Therefore, the DISTINCT operator keeps only one NULL in the result set.

Note that this behavior may be different between database products.

For example, the following statement returns the distinct phone numbers of employees:

SELECT DISTINCT
  phone_number
FROM
  employees
ORDER BY
  phone_number DESC;

Try it here.

 phone_number
--------------
 NULL
 650.501.2876
 650.501.1876
 650.124.1224
 650.123.4234

Notice that the query returns only one NULL in the result set.

Summary 

• Use DISTINCT operator in the SELECT clause to select unique values from one or more columns of a table.