SQL DISTINCT

SQL DISTINCT

Introduction to SQL DISTINCT operator

To remove duplicate rows from a result set, you use the DISTINCT operator in the SELECT clause as follows:

SELECT DISTINCT
    column1, column2, ...
FROM
    table1;Code language: SQL (Structured Query Language) (sql)

If you use one column after the DISTINCT operator, the DISTINCT operator uses values in that column to evaluate duplicates.

If you use two or more columns, the DISTINCT will use the combination of values in those columns to evaluate the duplicate.

Note that the DISTINCT only removes the duplicate rows from the result set. It doesn’t delete duplicate rows in the table.

If you want to select two columns and remove duplicates in one column, you should use the GROUP BY clause instead.

SQL DISTINCT examples

We will use the employees table in the sample database to demonstrate how the DISTINCT operator works.

employees_table

1) Using SQL DISTINCT operator on one column example

The following statement selects the salary data from the salary column of the employees table and sorts them from high to low:

SELECT 
    salary
FROM
    employees
ORDER BY salary DESC;Code language: SQL (Structured Query Language) (sql)

Try It

+----------+
| salary   |
+----------+
| 24000.00 |
| 17000.00 |
| 17000.00 |
| 14000.00 |
| 13500.00 |
| 13000.00 |
| 12000.00 |
| 12000.00 |
| 11000.00 |
| 10000.00 |
|  9000.00 |
|  9000.00 |
...

The result set has some duplicates. For example, 17000, 12000, and 9000.

The following statement uses the DISTINCT operator to select unique values from the salary column of the employees table:

SELECT 
    DISTINCT salary
FROM
    employees
ORDER BY salary DESC;Code language: SQL (Structured Query Language) (sql)

Try It

+----------+
| salary   |
+----------+
| 24000.00 |
| 17000.00 |
| 14000.00 |
| 13500.00 |
| 13000.00 |
| 12000.00 |
| 11000.00 |
| 10000.00 |
|  9000.00 |Code language: plaintext (plaintext)

As you can see, the result set doesn’t contain any duplicate salary values.

2) Using SQL DISTINCT operator on multiple columns example

The following statement selects the job id and salary from the employees table:

SELECT
	job_id,
	salary
FROM
	employees
ORDER BY
	job_id,
	salary DESC;Code language: SQL (Structured Query Language) (sql)

Try It

+--------+----------+
| job_id | salary   |
+--------+----------+
|      1 |  8300.00 |
|      2 | 12000.00 |
|      3 |  4400.00 |
|      4 | 24000.00 |
|      5 | 17000.00 |
|      5 | 17000.00 |
|      6 |  9000.00 |
|      6 |  8200.00 |
...Code language: plaintext (plaintext)

The result set has some duplicate rows e.g., job id 5 salary 17000. It means that there are two employees with the same job id and salary.

The following statement uses the DISTINCT operator to remove the duplicate values in job id and salary:

SELECT DISTINCT
	job_id,
	salary
FROM
	employees
ORDER BY
	job_id,
	salary DESC;Code language: SQL (Structured Query Language) (sql)

Try It

+--------+----------+
| job_id | salary   |
+--------+----------+
|      1 |  8300.00 |
|      2 | 12000.00 |
|      3 |  4400.00 |
|      4 | 24000.00 |
|      5 | 17000.00 |
|      6 |  9000.00 |
|      6 |  8200.00 |
...Code language: plaintext (plaintext)

Note that you still see the duplicate in the job_id column because the DISTINCT operator uses values from both job_id and salary to evaluate the duplicate, not just values in the job_id column.

SQL DISTINCT and NULL

In the database, NULL means unknown or missing data.

Unlike values like numbers, strings, dates, etc. NULL does not equal anything, even itself. The following expression will return unknown (or NULL):

NULL=NULLCode language: PHP (php)

Typically, the DISTINCT operator treats all NULL the same. Therefore, the DISTINCT operator keeps only one NULL in the result set.

Note that this behavior may be different between database products.

For example, the following statement returns the distinct phone numbers of employees:

SELECT DISTINCT phone_number
FROM employees
ORDER BY phone_number;Code language: SQL (Structured Query Language) (sql)

Try It

+--------------+
| phone_number |
+--------------+
| NULL         |
| 515.123.4444 |
| 515.123.4567 |
| 515.123.4568 |
| 515.123.4569 |
| 515.123.5555 |
...Code language: plaintext (plaintext)

Notice that the query returns only one NULL in the result set.

Summary

  • Use DISTINCT operator in the SELECT clause to remove duplicate rows from the result set.

Popular posts from this blog

SQL Syntax

Image
  SQL Syntax Summary : in this tutorial, we will introduce you to the SQL syntax that helps you understand the details of SQL statements. SQL is a declarative language, therefore, its syntax reads like a natural language. An SQL statement begins with a verb that describes the action, for example, SELECT, INSERT, UPDATE or DELETE. Following the verb are the subject and predicate. A predicate specifies conditions that can be evaluated as true, false, or unknown. See the following SQL statement: SELECT first_name FROM employees WHERE YEAR (hire_date) = 2000 ; Code language: SQL (Structured Query Language) ( sql ) As you see, it reads like a normal sentence. Get the first names of employees who were hired in 2000. The  SELECT first_name ,  FROM employees , and WHERE  are clauses in the SQL statement. Some clauses are mandatory e.g., the  SELECT  and  FROM  clauses whereas others are optional such as the  WHERE  clause. Because SQL was designed specifically for the non-tec
Read more

SQL FETCH

Image
Skip to content SQL FETCH Summary : in this tutorial, you will learn how to use the SQL   FETCH   clause to limit the number of rows returned by a query. Introduction to SQL FETCH clause To limit the number of rows returned by a query, you use the  LIMIT  clause. The  LIMIT  clause is widely supported by many database systems such as MySQL, H2, and HSQLDB. However, the  LIMIT  clause is not in SQL standard. SQL:2008 introduced the  OFFSET FETCH  clause which has a similar function to the  LIMIT  clause. The  OFFSET FETCH  clause allows you to skip  N  first rows in a result set before starting to return any rows. The following shows the syntax of the SQL  FETCH  clause: OFFSET offset_rows { ROW | ROWS } FETCH { FIRST | NEXT } [ fetch_rows ] { ROW | ROWS } ONLY Code language: SQL (Structured Query Language) ( sql ) In this syntax: The  ROW  and  ROWS ,  FIRST  and  NEXT  are the synonyms. Therefore, you can use them interchangeably. The  offset_rows  is an integer number which must be
Read more

SQL LIMIT

Image
Skip to SQL LIMIT Summary : in this tutorial, you’ll learn to use the SQL  LIMIT  clause to limit the number of rows returned from a query. Introduction to SQL LIMIT clause To limit the number of rows returned by a  select  statement, you use the  LIMIT  and  OFFSET  clauses. The following shows the syntax of  LIMIT  &  OFFSET  clauses: SELECT column_list FROM table1 ORDER BY column_list LIMIT row_count OFFSET offset ; Code language: SQL (Structured Query Language) ( sql ) In this syntax: The  LIMIT row_count  determines the number of rows ( row_count ) returned by the query. The  OFFSET offset  clause skips the  offset  rows before beginning to return the rows. The  OFFSET  clause is optional. If you omit it, the query will return the row_count rows from the first row returned by the  SELECT  clause. When you use the  LIMIT  clause, it is important to use an  ORDER BY  clause to ensure the order of rows in the result set. Not all database systems support the  LIM
Read more