How to use distinct in SQL?

SQL DISTINCT clause is used to remove the duplicates columns from the result set.

The distinct keyword is used with select keyword in conjunction. It is helpful when we avoid duplicate values present in the specific columns/tables. The unique values are fetched when we use the distinct keyword.

• SELECT DISTINCT returns only distinct (different) values.
• DISTINCT eliminates duplicate records from the table.
• DISTINCT can be used with aggregates: COUNT, AVG, MAX, etc.
• DISTINCT operates on a single column.
• Multiple columns are not supported for DISTINCT.

Syntax:

Parameters:

Expressions: The columns or calculations that we want to retrieve are called expression.

Tables: The tables that we want to retrieve the records. There is only one table in the FROM clause.

WHERE conditions: The conditions may meet for the records which are selected and it is optional.

Note:

• When one expression is provided in the DISTINCT clause then the query will return the unique values of the expressions.
• The query will retrieve the unique combinations for the listed expressions if more than one expression is provided in the DISTINCT clause here.
• In SQL, the DISTINCT clause cannot ignore the NULL values. So when we use the DISTINCT clause in the SQL statement, our result set will include NULL as a distinct value.

Example:

Consider the following EMPLOYEES table.

First, let us see the following SELECT query returns the duplicate salary records.

When we execute the above SQL query, it fetches all the records including the duplicate records. In the above table, salary of Newton and Johnson is same 20000.

Now, let us use the DISTINCT keyword with the above SELECT query.

The above SQL query removes the duplicate records and shows the following result.

Example: Finding Unique Values in the Column

Look at the DISTINCT clause to find the unique values within one column in the table.

We have a table called suppliers with the following data:

From the above table, we are going to find the unique states.

These are six the records.

The example returns the unique state from suppliers table and removes the duplicate records from the result set.

Example: Finding Unique Values in Multiple Column

The SQL DISTINCT clause is used to remove the duplicate records from many fields in the SELECT statement.

Enter the SQL statement:

Output:

These are 8 records:

The example returns each unique city and state combination. We see the Redwood City and California, appears in the result set.

Example: DISTINCT Clause handles NULL Values

The DISTINCT clause considers NULL to the unique value in SQL. We have a table called products which contains the below data.

Select the unique values from the field fruit_id which contains the null value. Enter the below SQL syntax:

There are four records selected. These are the results which we see below:

In the above example, the query returns the unique values that are in the category_id column. We see by the first row in the result set, NULL is an exceptional value which is returned by the DISTINCT clause.