The SELECT DISTINCT Clause in SQL

Your table may contain duplicate values in a column and in certain scenarios you may require fetching only unique records from the table.

To remove the duplicate records for the data fetched with SELECT statement, you may use the DISTINCT clause as shown in the examples below.

A demo of simple SELECT – DISTINCT

In the first example, I used the DISTINCT clause with SELECT statement to retrieve only unique names from our demo table, sto_emp_salary_paid. This table stores employee salaries along with their names. So, the duplicate occurrence of employee names occurs in the table.

By using DISTINCT clause, we get only unique employee names:

Query:

SELECT DISTINCT(emp_name) FROM sto_emp_salary_paid;

SQL DISTINCT

(Applies to SQL Server and MySQL databases)

Using WHERE clause with DISTINCT

In this example, I used the WHERE clause with SELECT/DISTINCT statement to retrieve only those unique employees to whom paid salary is greater than or equal to 4500. See the query and result-set:

Query:

SQL DISTINCT WHERE

The example of COUNT function with DISTINCT

You may also use the COUNT SQL function for getting the number of records as using the DISTINCT clause. The function returns only count for the rows returned after DISTINCT clause.

For the demo, I am using employee table that stores the information about the employees. Three queries are used in the demo as follows:

  • The First query returns the complete record from the table
  • Second query gets the number of employees by using ID (COUNT and DISTINCT)
  • While the third returns the unique employee names by using emp_name column.

The three queries are:

SQL DISTINCT COUNT

The DISTINCT clause with GROUP BY example

The following query fetches the records from the same table as used in above examples and groups the employees paid salaries. For that, the GROUP BY and DISTINCT clauses are used as follows:

The Query:

SQL DISTINCT GROUP-BY

The record for the “Jimmy” is appearing twice as it has two different IDs.

Using HAVING clause with DISTINCT

As using GROUP BY clause with DISTINCT, you may also add HAVING clause for fetching records. In the following query, the HAVING clause is added in the above example and we will fetch the records which SUM is greater than 5000.

The query:

SQL DISTINCT HAVING

The DISTINCT clause with ORDER BY example

The SQL ORDER BY clause can be used with the DISTINCT clause for sorting the results after removing duplicate values. See the query and output below:

The result:

SQL DISTINCT ORDER-BY

Using multiple columns in the DISTINCT clause

You may also specify two or more columns as using the SELECT – DISTINCT clause. As such, our example table contains duplicate values for employees and their IDs, so it will be good learning to see how DISTINCT clause returns the records as using both these columns in the single query.

For seeing the difference, I first wrote a query with DISTINCT (emp_name) which is followed by using both columns:

The Query:

The results for complete table, DISTINCT emp_name and DISTINCT emp_name,emp_id queries:

SQL DISTINCT multi columns