MySQL Cookbook, 2nd Edition

Using DISTINCT to Eliminate Duplicates

Problem

You want to know which values are present in a set of values, without displaying duplicate values multiple times. Or you want to know how many distinct values there are.

Solution

Use DISTINCT to select unique values or COUNT(DISTINCT) to count them.

Discussion

One summary operation that doesn’t use aggregate functions is to determine which values or rows are contained in a dataset by eliminating duplicates. Do this with DISTINCT (or DISTINCTROW, which is synonymous). DISTINCT is useful for boiling down a query result, and often is combined with ORDER BY to place the values in more meaningful order. For example, to determine the names of the drivers listed in the driver_log table, use the following statement:

mysql>SELECT DISTINCT name FROM driver_log ORDER BY name;
+-------+
| name  |
+-------+
| Ben   |
| Henry |
| Suzi  |
+-------+

A statement without DISTINCT produces the same names, but is not nearly as easy to understand, even with a small dataset:

mysql>SELECT name FROM driver_log;
+-------+
| name  |
+-------+
| Ben   |
| Suzi  |
| Henry |
| Henry |
| Ben   |
| Henry |
| Suzi  |
| Henry |
| Ben   |
| Henry |
+-------+

To determine how many different drivers there are, use COUNT(DISTINCT):

mysql>SELECT COUNT(DISTINCT name) FROM driver_log;
+----------------------+
| COUNT(DISTINCT name) |
+----------------------+
|                    3 |
+----------------------+

COUNT(DISTINCT) ignores NULL values. Should you wish to count NULL as one of the values in the set if it’s ...