This SQL tutorial explains how to use the SQL DISTINCT clause with syntax and examples.
The SQL DISTINCT clause is used to eliminate duplicates from the end 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
expressions The columns or calculations that you desire to retrieve. tables The tables that you wish to retrieve information from. There have to be at least one desk listed in the FROM clause. WHERE conditions Optional. The conditions that need to be met for the data to be selected.
When only one expression is furnished in the DISTINCT clause, the query will return the special values for that expression. When extra than one expression is supplied in the DISTINCT clause, the query will retrieve special mixtures for the expressions listed. In SQL, the DISTINCT clause doesn’t ignore NULL values. So when the usage of the DISTINCT clause in your SQL statement, your end result set will encompass NULL as a distinct value.
DDL/DML for Examples
If you favor to comply with alongside with this tutorial, get the DDL to create the tables and the DML to populate the data. Then strive the examples in your very own database!
Example – Finding Unique Values in a Column
Let’s seem at how to use the DISTINCT clause to locate the special values inside one column in a table.
In this example, we have a table called suppliers with the following data:
supplier_id supplier_name town 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 locate 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 data selected. These are the outcomes that you must see:
state Arkansas California Georgia Texas Washington Wisconsin
This instance would return all special nation values from the suppliers table and do away with any duplicates from the result set. As you can see, the kingdom of California solely appears as soon as in the end result set as an alternative of 4 times.
Example – Finding Unique Values in Multiple Columns
Next, let’s look at how to use the SQL DISTINCT clause to dispose of duplicates from more than one discipline in a SELECT statement.
Using the identical suppliers table from the preceding example, enter the following SQL statement:
SELECT DISTINCT city, state FROM suppliers ORDER BY city, state;
There will be eight documents selected. These are the effects that you ought to see:
town state Irving Texas Mountain View California Racine Wisconsin Redmond Washington Redwood City California Springdale Arkansas Thomasville Georgia Westlake Village California
This example would return every special city and kingdom combination. In this case, the DISTINCT applies to every area listed after the DISTINCT keyword. As you can see, Redwood City, California only seems once in the end 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 reply is yes. Let’s discover this further.
In this example, we have a table referred to as 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 pick the unique values from the category_id discipline which includes 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 consequences that you must see:
In this example, the query would return the special values discovered in the category_id column. As you can see by means of the first row in the end result set, NULL is a special fee that is back via the DISTINCT clause.