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:
- Writing Basic SQL SELECT statements
- SQL Arithmetic Operators
- SQL Column Aliases
- SQL String Concatenation
- SQL DISTINCT keyword
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.
