SQL DISTINCT clause

In this tutorial, you will learn how to remove duplicate values from the result set.

Retrieve different values

When retrieving data from a database table, the result set may contain duplicate rows or values. If you want to remove these duplicate values, you can specify the keyword DISTINCT directly after the SELECT keyword, as shown below:

Syntax

The DISTINCT clause is used to remove duplicate rows from the result set:

SELECT DISTINCT column_list FROM table_name;

Here,column_listis a list of database table column names or field names separated by commas (for examplename,age,countryetc.).

Let's look at some examples to demonstrate how it actually works.

Suppose we have acustomerstable, which contains the following records:

+---------+--------------------+-----------+-------------+
|cust_id|cust_name                                 |city                 |postal_code |
+---------+--------------------+-----------+-------------+
|　　　　　　　1　|Maria Anders                         |Berlin            |　12209　　　　　　　|
|　　　　　　　2　|Fran Wilson                           |Madrid            |　28023　　　　　　　|
|　　　　　　　3　|Dominique Perrier                   |Paris             |　75016　　　　　　　|
|　　　　　　　4　|Martin Blank                         |Turin             |　10100                               |
|　　　　　　　5　|Thomas Hardy                       |Portland         |　97219　　　　　　　|
|　　　　　　　6　|Christina Aguilera|Madrid           |　28001　　　　　　　|
+---------+--------------------+-----------+-------------+

Now execute the following statement, which returns thecityAll rows in the column.

SELECT city FROM customers;

After executing, you will get the following output:

+-----------+
| city |
+-----------+
| Berlin |
| Madrid |
| Paris |
| Turin |
| Portland |
| Madrid |
+-----------+

If you take a close look at the output, you will find that the city 'Madrid' appears twice in our results. That's not good, is it? Alright, let's solve this problem.

deleting duplicate data

The following statement is used for DISTINCT incustomersList of all cities generated in the table.

SELECT DISTINCT city FROM customers;

After executing the above command, you will get the following output:

+-----------+
| city |
+-----------+
| Berlin |
| Madrid |
| Paris |
| Turin |
| Portland |
+-----------+

As you can see, there are no duplicate values in this result set.