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;Code language: SQL (Structured Query Language) (sql)

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;Code language: SQL (Structured Query Language) (sql)

Try it

+----------+
| 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;Code language: SQL (Structured Query Language) (sql)

Try it

  salary
----------
 24000.00
 17000.00
 14000.00
 13500.00
 13000.00
 12000.00
 11000.00
 10000.00
  9000.00
...Code language: plaintext (plaintext)

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;Code language: SQL (Structured Query Language) (sql)

Try it

 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
...Code language: plaintext (plaintext)

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;Code language: SQL (Structured Query Language) (sql)

Try it

 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
...Code language: plaintext (plaintext)

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;Code language: PHP (php)

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;Code language: SQL (Structured Query Language) (sql)

Try it

 phone_number
--------------
 NULL
 650.501.2876
 650.501.1876
 650.124.1224
 650.123.4234
...Code language: plaintext (plaintext)

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.

Databases #

Was this tutorial helpful ?