MySQL SELECT DISTINCT clause

Overview

In this tutorial, we will learn MySQL SELECT DISTINCT clause with examples.We can use DISTINCT clause remove duplicates to produce the SELECT query result set in which every row is unique. If a query returns a result that contains duplicate rows so that the result set contains only unique rows.

Syntax

The simple DISTINCT clause SELECT statement has the following syntax:

mysql>SELECT DISTINCT column_list

FROM <table_name>;

We can use this clause after the SELECT but before the column list. When processing the SELECT statement with DISTINCT clause MySQL will compare whole rows.

Examples

In this example, we will query the continents from the Country table from the world MySQL database. To know the table definition and the columns use the following query:

mysql> DESC Country;

To know the number of rows in the table:

mysql> SELECT COUNT(*) FROM Country;
+———-+
| COUNT(*) |
+———-+
| 239 |
+———-+
1 row in set (0.04 sec)

MySQL World Country Table Row Count

Run the following query to get the Continents.

mysql>SELECT Continent FROM Country;

Notice that the query returns duplicate rows in the result set. Now, run the following query with DISTINCT clause. Add the DISTINCT clause after the SELECT keyword in the query:

mysql> SELECT DISTINCT Continent FROM Country;
+—————+
| Continent |
+—————+
| North America |
| Asia |
| Africa |
| Europe |
| South America |
| Oceania |
| Antarctica |
+—————+
7 rows in set (0.02 sec)

Run the query without the DISTINCT clause and notice that the query returns duplicate rows. When we add DISTINCT clause to the statement, it removes the duplicates and returns only the unique rows.

MySQL SELECT DISTINCT clause