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:

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:

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:

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.