sql-distinct

SQL DISTINCT

This SQL tutorial explains how to use the SQL DISTINCT clause with syntax and examples.

Description

The SQL DISTINCT clause is used to remove duplicates from the result set of a SELECT statement.

Syntax

The syntax for the DISTINCT clause in SQL is:

SELECT DISTINCT expressions
FROM tables
[WHERE conditions];

Parameters or Arguments

expressions
The columns or calculations that you wish to retrieve.
tables
The tables that you wish to retrieve records from. There must be at least one table listed in the FROM clause.
WHERE conditions
Optional. The conditions that must be met for the records to be selected.

Note

  • When only one expression is provided in the DISTINCT clause, the query will return the unique values for that expression.
  • When more than one expression is provided in the DISTINCT clause, the query will retrieve unique combinations for the expressions listed.
  • In SQL, the DISTINCT clause doesn’t ignore NULL values. So when using the DISTINCT clause in your SQL statement, your result set will include NULL as a distinct value.

DDL/DML for Examples

If you want to follow along with this tutorial, get the DDL to create the tables and the DML to populate the data. Then try the examples in your own database!

Example – Finding Unique Values in a Column

Let’s look at how to use the DISTINCT clause to find the unique values within one column in a table.

In this example, we have a table called suppliers with the following data:

supplier_idsupplier_namecitystate
100MicrosoftRedmondWashington
200GoogleMountain ViewCalifornia
300OracleRedwood CityCalifornia
400Kimberly-ClarkIrvingTexas
500Tyson FoodsSpringdaleArkansas
600SC JohnsonRacineWisconsin
700Dole Food CompanyWestlake VillageCalifornia
800Flowers FoodsThomasvilleGeorgia
900Electronic ArtsRedwood CityCalifornia

Let’s find all of the unique states in the suppliers table. Enter the following SQL statement:

There will be 6 records selected. These are the results that you should see:SELECT DISTINCT state
FROM suppliers
ORDER BY state;
state
Arkansas
California
Georgia
Texas
Washington
Wisconsin

This example would return all unique state values from the suppliers table and remove any duplicates from the result set. As you can see, the state of California only appears once in the result set instead of four times.

Example – Finding Unique Values in Multiple Columns

Next, let’s look at how to use the SQL DISTINCT clause to remove duplicates from more than one field in a SELECT statement.

Using the same suppliers table from the previous example, enter the following SQL statement:

SELECT DISTINCT city, state
FROM suppliers
ORDER BY city, state;

There will be 8 records selected. These are the results that you should see:

citystate
IrvingTexas
Mountain ViewCalifornia
RacineWisconsin
RedmondWashington
Redwood CityCalifornia
SpringdaleArkansas
ThomasvilleGeorgia
Westlake VillageCalifornia

This example would return each unique city and state combination. In this case, the DISTINCT applies to each field listed after the DISTINCT keyword. As you can see, Redwood City, California only appears once in the result set instead of twice.

Example – How the DISTINCT Clause handles NULL Values

Finally, does the DISTINCT clause consider NULL to be a unique value in SQL? The answer is yes. Let’s explore this further.

In this example, we have a table called products with the following data:

product_idproduct_namecategory_id
1Pear50
2Banana50
3Orange50
4Apple50
5Bread75
6Sliced Ham25
7KleenexNULL

Now let’s select the unique values from the category_id field which contains a NULL value. Enter the following SQL statement:

SELECT DISTINCT category_id
FROM products
ORDER BY category_id;

There will be 4 records selected. These are the results that you should see:

category_id
NULL
25
50
75

In this example, the query would return the unique values found in the category_id column. As you can see by the first row in the result set, NULL is a unique value that is returned by the DISTINCT clause.