You want to determine whether a table contains duplicates, and to what extent they occur. Or you want to see the rows that contain the duplicated values.
Use a counting summary that looks for and displays duplicated values. To see the rows in which the duplicated values occur, join the summary to the original table to display the matching rows.
Suppose that your web site includes a sign-up page that enables visitors to add themselves to your mailing list to receive periodic product catalog mailings. But you forgot to include a unique index in the table when you created it, and now you suspect that some people are signed up multiple times. Perhaps they forgot they were already on the list, or perhaps people added friends to the list who were already signed up. Either way, the result of having duplicate rows is that you mail out duplicate catalogs. This is an additional expense to you, and it annoys the recipients. This section discusses how to find out if duplicates are present in a table, how prevalent they are, and how to display the duplicated rows. (For tables that do contain duplicates, Eliminating Duplicates from a Table describes how to eliminate them.)
To determine whether duplicates occur in a table, use a counting
summary (a topic covered in Chapter 8). Summary
techniques can be applied to identifying and counting duplicates by
grouping rows with
GROUP
BY
and counting the rows in each group
using
COUNT()
. For
the examples here, assume that catalog recipients are listed in a
table named catalog_list
that has
the following contents:
mysql>SELECT * FROM catalog_list;
+-----------+-------------+--------------------------+
| last_name | first_name | street |
+-----------+-------------+--------------------------+
| Isaacson | Jim | 515 Fordam St., Apt. 917 |
| Baxter | Wallace | 57 3rd Ave. |
| McTavish | Taylor | 432 River Run |
| Pinter | Marlene | 9 Sunset Trail |
| BAXTER | WALLACE | 57 3rd Ave. |
| Brown | Bartholomew | 432 River Run |
| Pinter | Marlene | 9 Sunset Trail |
| Baxter | Wallace | 57 3rd Ave., Apt 102 |
+-----------+-------------+--------------------------+
Suppose that you want to define “duplicate” using
the last_name
and first_name
columns. That is, recipients with
the same name are assumed to be the same person. The following
statements are typical of those used to characterize the table and to
assess the existence and extent of duplicate values:
The total number of rows in the table:
mysql>SELECT COUNT(*) AS rows FROM catalog_list;
+------+
| rows |
+------+
| 8 |
+------+
The number of distinct names:
mysql>SELECT COUNT(DISTINCT last_name, first_name) AS 'distinct names'
->FROM catalog_list;
+----------------+ | distinct names | +----------------+ | 5 | +----------------+
The number of rows containing duplicated names:
mysql>SELECT COUNT(*) - COUNT(DISTINCT last_name, first_name)
->AS 'duplicate names'
->FROM catalog_list;
+-----------------+ | duplicate names | +-----------------+ | 3 | +-----------------+
The fraction of the rows that contain unique or nonunique names:
mysql>SELECT COUNT(DISTINCT last_name, first_name) / COUNT(*)
->AS 'unique',
->1 - (COUNT(DISTINCT last_name, first_name) / COUNT(*))
->AS 'nonunique'
->FROM catalog_list;
+--------+------------+ | unique | nonunique | +--------+------------+ | 0.6250 | 0.3750 | +--------+------------+
These statements help you characterize the extent of duplicates,
but they don’t show you which values are duplicated. To see the
duplicated names in the catalog_list
table, use a summary statement
that displays the nonunique values along with the counts:
mysql>SELECT COUNT(*) AS repetitions, last_name, first_name
->FROM catalog_list
->GROUP BY last_name, first_name
->HAVING repetitions > 1;
+-------------+-----------+------------+ | repetitions | last_name | first_name | +-------------+-----------+------------+ | 3 | Baxter | Wallace | | 2 | Pinter | Marlene | +-------------+-----------+------------+
The statement includes a
HAVING
clause that
restricts the output to include only those names that occur more than
once. (If you omit the clause, the summary lists unique names as well,
which is useless when you’re interested only in duplicates.) In
general, to identify sets of values that are duplicated, do the
following:
Determine which columns contain the values that may be duplicated.
List those columns in the column selection list, along with
COUNT(*)
.
List the columns in the GROUP
BY
clause as well.
Add a HAVING
clause that
eliminates unique values by requiring group counts to be greater
than one.
Queries constructed that way have the following form:
SELECT COUNT(*),column_list
FROMtbl_name
GROUP BYcolumn_list
HAVING COUNT(*) > 1
It’s easy to generate duplicate-finding queries like that within
a program, given database and table names and a nonempty set of column
names. For example, here is a Perl function make_dup_count_query()
that generates
the proper query for finding and counting duplicated values in the
specified columns:
sub make_dup_count_query { my ($db_name, $tbl_name, @col_name) = @_; return ( "SELECT COUNT(*)," . join (",", @col_name) . " FROM $db_name.$tbl_name" . " GROUP BY " . join (",", @col_name) . " HAVING COUNT(*) > 1" ); }
make_dup_count_query()
returns the query as a string. If you invoke it like this:
$str = make_dup_count_query ("cookbook", "catalog_list", "last_name", "first_name");
the resulting value of $str
is:
SELECT COUNT(*),last_name,first_name FROM cookbook.catalog_list GROUP BY last_name,first_name HAVING COUNT(*) > 1
What you do with the query string is up to you. You can execute
it from within the script that creates it, pass it to another program,
or write it to a file for execution later. The dups directory of the recipes
distribution contains a script named
dup_count.pl that you can use to
try the function (as well as some translations into other languages).
Eliminating Duplicates from a Table discusses use of the
make_dup_count_query()
function to implement a duplicate-removal technique.
Summary techniques are useful for assessing the existence of
duplicates, how often they occur, and displaying which values are
duplicated. But if duplicates are determined using only a subset of a
table’s columns, a summary in itself cannot display the entire content
of the rows that contain the duplicate values. (For example, the
summaries shown thus far display counts of duplicated names in the
catalog_list
table or the names
themselves, but don’t show the addresses associated with those names.)
To see the original rows containing the duplicate names, join the
summary information to the table from which it’s generated. The
following example shows how to do this to display the catalog_list
rows that contain duplicated
names. The summary is written to a temporary table, which then is
joined to the catalog_list
table to
produce the rows that match those names:
mysql>CREATE TABLE tmp
->SELECT COUNT(*) AS count, last_name, first_name FROM catalog_list
->GROUP BY last_name, first_name HAVING count > 1;
mysql>SELECT catalog_list.*
->FROM tmp INNER JOIN catalog_list USING(last_name, first_name)
->ORDER BY last_name, first_name;
+-----------+------------+----------------------+ | last_name | first_name | street | +-----------+------------+----------------------+ | Baxter | Wallace | 57 3rd Ave. | | BAXTER | WALLACE | 57 3rd Ave. | | Baxter | Wallace | 57 3rd Ave., Apt 102 | | Pinter | Marlene | 9 Sunset Trail | | Pinter | Marlene | 9 Sunset Trail | +-----------+------------+----------------------+
3.15.12.34