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: ...
Get MySQL Cookbook now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.