This SQL tutorial explains how to use the SQL DISTINCT clause with syntax and examples.
The SQL DISTINCT clause is used to remove duplicates from the result set of a SELECT statement.
The syntax for the DISTINCT clause in SQL is:
SELECT DISTINCT expressions FROM tables [WHERE conditions];
Parameters or Arguments
- The columns or calculations that you wish to retrieve.
- 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.
- 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:
|700||Dole Food Company||Westlake Village||California|
|900||Electronic Arts||Redwood City||California|
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;
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:
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:
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:
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.