fbpx

MySQL DISTINCT Keyword

This SQL tutorial focuses on the SQL DISTINCT keyword in MySQL, and provides explanations, examples and exercises. For this lesson’s exercises, use this link.

This tutorial is a part of several posts explaining how to write basic queries in MySQL. To read additional posts regarding this subject, please use the following links:


The MySQL DISTINCT Keyword

The default display of MySQL queries is all rows, including duplicate rows. The MySQL DISTINCT keyword in the SELECT clause is used to eliminate duplicate rows and display a unique list of values.

For example: let us assume we have a Books table, which contains a list of books written by different authors: 4 books by Frank Herbert, 3 books by Steven king, and another 3 books by Virginia Woolf. You were asked to display a unique list of authors:

SELECT author
FROM   books

author
-------------
Frank Herbert
Frank Herbert
Frank Herbert
Frank Herbert
Steven King
Steven King
Steven King
Virginia Woolf
Virginia Woolf
Virginia Woolf

The above MySQL statement is not very helpful since it retrieves 10 rows (Frank Herbert returns 4 times, Steven King Returns 3 times, and Virginia Woolf returns 3 times), whereas our intention was to display only 3 authors.

To resolve this issue and retrieve only unique values, use the MySQL DISTINCT keyword:

SELECT DISTINCT column_name
FROM     table_name 

SELECT DISTINCT author
FROM   books

The result:

author
-------------
Frank Herbert
Steven King
Virginia Woolf

Note : The MySQL DISTINCT keyword should be included immediately after the MySQL SELECT keyword.

MySQL DISTINCT Statement on Multiple Columns

After the MySQL DISTINCT keyword, it is possible to specify more than one column. The MySQL DISTINCT qualifier affects all selected columns, in so doing, the result will display the unique combination of values from the different columns. For example, a list of authors from different cities and countries:

Author Name City Country
John Smith Liverpool England
Sophia Williams London England
Emma Miller Liverpool England
Olivia Wilson Paris France
Logan Davis Paris France

You can see that there are two authors from Liverpool-England, two authors from Paris-France, and another author from London-England.

Executing the MySQL DISTINCT statement on the Country column results in retrieving 2 rows (England,Paris).

SELECT DISTINCT country
FROM            authors

Executing the MySQL DISTINCT statement on the City column results in retrieving 3 rows (Liverpool, London, paris).

SELECT DISTINCT city 
FROM            authors

If you execute the MySQL DISTINCT statement on both the City and Country columns:

SELECT DISTINCT city , country
FROM            authors

Three results are retrieved – Liverpool England, London England, Paris France.

UpScale Analytics is one of the largest platforms in the world for learning SQL by doing, consisting over 300 SQL exercises at different levels (including solutions), by topics, across over 100 different datasets. More…