Basic about DISTINCT Clause – using and example

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

Description

The SQL DISTINCT clause is used to eliminate duplicates from the end 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 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.

Note

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!

Get DDL/DML

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:

Try It

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:

Try It

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:

Try It

SELECT DISTINCT category_id
FROM products
ORDER BY category_id;

There will be 4 records selected. These are the consequences that you must see:

category_id

NULL

25

50

75

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.