MySQL SELECT DISTINCT clause

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

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)

 

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.

 

Note that DISTINCT clause treats all NULL values for the given column as having the same value.

 

MySQL Tutorials

MySQL Tutorials on this website:

• https://www.testingdocs.com/mysql-tutorials-for-beginners/