SQL DISTINCT Clause

The SQL DISTINCT clause is used to return only distinct (different) values from the result set of a SELECT statement.

The result set retrieved from a database table may contain duplicate rows or values. To remove these duplicate values, you can use the DISTINCT clause after the SELECT keyword.

Syntax

The syntax for the DISTINCT clause to eliminate the duplicate records is as follows:

SELECT DISTINCT column1_name, column2_name, columnN_name
FROM table_name;

Here the DISTINCT clause can accept one column or multiple columns.

Note:

• When only one column is passed to the DISTINCT clause, the query will return unique values for that column.
• When multiple columns are passed to the DISTINCT clause, the query will return unique combinations for the columns passed.

• When only one column is passed to the DISTINCT clause, the query will return unique values for that column.
• When multiple columns are passed to the DISTINCT clause, the query will return unique combinations for the columns passed.

Finding Unique Values in a Column

Let us see how to use the DISTINCT clause to find unique values within one column in a table.

Suppose we have a "customer" table in our database with the following records:

Now let us find all the unique country in the "customer" table by running the following command:

SELECT DISTINCT country 
FROM customer;

The command above will return all unique country from the "customer" table and remove any duplicates from the result set. There will be 3 records selected as the following:

+---------------+
| country       |
+---------------+
| United States |
| India         |
| Switzerland   |
+---------------+

As you can see, "United States" country only appears once in the result set instead of three times.

Finding Unique Values in Multiple Columns

Now, let us see how to use the DISTINCT clause to remove duplicate values from more than one column in a SELECT statement.

We will use the same "customer" table from the previous section, run the following SQL statement:

SELECT DISTINCT city, country 
FROM customer

The above command will return each unique city and country combination. There will be 4 records as following:

+-------------+---------------+
| city        | country       |
+-------------+---------------+
| Agra        | India         |
| Basel       | Switzerland   |
| Seatlle     | United States |
| Los Angeles | United States |
+-------------+---------------+

As you can see, "Seattle City, United States Country" only appears once in the result set instead of twice.

SQL DISTINCT clause with NULL values

The DISTINCT clause does not ignore NULL values. So when using the DISTINCT clause in a SQL statement, the result set may include NULL as a distinct value.

The DISTINCT operator treats NULL values to be duplicated to each other. So when using the SELECT DISTINCT, only one NULL value will be returned, and the others will be removed from the result set.

In the example bellow, we will use a table called "employee" with the following data:

Now let us select the unique values from the "department_id" which contains two records with a NULL value. Run the following SQL statment:

SELECT DISTINCT department_id
FROM employee;

The above SQL statement will return the unique values found in the "department_id" column like the following:

+---------------+
| department_id |
+---------------+
| NULL          |
| 10            |
| 15            |
+---------------+

As you can see, NULL is a unique value that is returned by the DISTINCT clause.