totn SQL

SQL: DISTINCT Clause

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!

Get DDL/DML

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_id supplier_name city state
100 Microsoft Redmond Washington
200 Google Mountain View California
300 Oracle Redwood City California
400 Kimberly-Clark Irving Texas
500 Tyson Foods Springdale Arkansas
600 SC Johnson Racine Wisconsin
700 Dole Food Company Westlake Village California
800 Flowers Foods Thomasville Georgia
900 Electronic Arts Redwood City California

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

Try It
SELECT DISTINCT state
FROM suppliers
ORDER BY state;

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

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:

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

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

city state
Irving Texas
Mountain View California
Racine Wisconsin
Redmond Washington
Redwood City California
Springdale Arkansas
Thomasville Georgia
Westlake Village California

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_id product_name category_id
1 Pear 50
2 Banana 50
3 Orange 50
4 Apple 50
5 Bread 75
6 Sliced Ham 25
7 Kleenex NULL

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

Try It
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.