Using DISTINCT to Eliminate Duplicates
Problem
You want to know which values are present in a set of values, without listing duplicate values a bunch of 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
A 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, if you want to know the names of
the drivers listed in the driver_log table, use
the following query:
mysql> SELECT DISTINCT name FROM driver_log ORDER BY name;
+-------+
| name |
+-------+
| Ben |
| Henry |
| Suzi |
+-------+A query without DISTINCT produces the same names,
but is not nearly as easy to understand:
mysql> SELECT name FROM driver_log;
+-------+
| name |
+-------+
| Ben |
| Suzi |
| Henry |
| Henry |
| Ben |
| Henry |
| Suzi |
| Henry |
| Ben |
| Henry |
+-------+If you want to know 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. If you also want to count
NULL as one of the values in the set if it’s present, do this: ...