MySQL DISTINCT Clause


Introduction

When querying data from a table, we may get duplicate rows. In order to remove these duplicate rows, we use the DISTINCT clause in the SELECT statement.

Here is the syntax of the DISTINCT clause:

1
2
SELECT DISTINCT select_list
FROM table_name;

Examples

Consider the following example.

1
2
3
4
5
6
7
8
9
10
11
12
mysql> SELECT DISTINCT artist_name FROM
 -> artist INNER JOIN album USING (artist_id);
+---------------------------+
| artist_name               |
+---------------------------+
| New Order                 |
| Nick Cave & The Bad Seeds |
| Miles Davis               |
| The Rolling Stones        |
| The Stone Roses           |
| Kylie Minogue             |
+---------------------------+

The query finds artists who have made albums—by joining together artist and album with an INNER JOIN clause—and reports one example of each artist. We can see that we have six artists in our database for whom we own albums.

If we remove the DISTINCT clause, we get one row of output for each album we own:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
mysql> SELECT artist_name FROM
 -> artist INNER JOIN album USING (artist_id);
+---------------------------+
| artist_name               |
+---------------------------+
| New Order                 |
| New Order                 |
| New Order                 |
| New Order                 |
| New Order                 |
| New Order                 |
| New Order                 |
| Nick Cave & The Bad Seeds |
| Miles Davis               |
| Miles Davis               |
| The Rolling Stones        |
| The Stone Roses           |
| Kylie Minogue             |
+---------------------------+

The DISTINCT clause applies to the query output and removes rows that have identical values in the columns selected for output in the query. If we rephrase the previous query to output both artist_name and album_name (but otherwise don’t change the JOIN clause and still use DISTINCT), we will get all 13 rows in the output:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
mysql> SELECT DISTINCT artist_name, album_name FROM
 -> artist INNER JOIN album USING (artist_id);
+---------------------------+------------------------------------------+
| artist_name               | album_name                               |
+---------------------------+------------------------------------------+
| New Order                 | Retro - John McCready FAN                |
| New Order                 | Substance (Disc 2)                       |
| New Order                 | Retro - Miranda Sawyer POP               |
| New Order                 | Retro - New Order / Bobby Gillespie LIVE |
| New Order                 | Power, Corruption & Lies                 |
| New Order                 | Substance 1987 (Disc 1)                  |
| New Order                 | Brotherhood                              |
| Nick Cave & The Bad Seeds | Let Love In                              |
| Miles Davis               | Live Around The World                    |
| Miles Davis               | In A Silent Way                          |
| The Rolling Stones        | Exile On Main Street                     |
| The Stone Roses           | Second Coming                            |
| Kylie Minogue             | Light Years                              |
+---------------------------+------------------------------------------+

Because none of the rows are identical, no duplicates are removed using DISTINCT.




Related Posts

MySQL INNER JOIN Clause

The INNER JOIN keyword selects records that have matching values...

MySQL Aliases

Aliases are nicknames. They give us a shorthand way of...

MySQL OR Operator

The OR operator combines two Boolean expressions and returns true...

MySQL LIMIT Operator

The LIMIT clause is used in the SELECT statement to...

MySQL LIKE Operator

The LIKE operator is used in a WHERE clause to...

MySQL IS NULL Operator

To test whether a value is NULL or not, we...