MySQL Cookbook

14.4 Counting and Identifying Duplicates

14.4.1 Problem

You want to find out if a table contains duplicates, and to what extent they occur. Or you want to see the records that contain the duplicated values.

14.4.2 Solution

Use a counting summary that looks for and displays duplicated values. To see the records in which the duplicated values occur, join the summary to the original table to display the matching records.

14.4.3 Discussion

Suppose that your web site includes a sign-up page that allows 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 the duplicate records 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 records. (For tables that do contain duplicates, Recipe 14.7 describes how to eliminate them.)

To determine whether or not duplicates occur in a table, use a counting summary, a topic covered in Chapter 7. Summary techniques can be applied to identifying and counting duplicates by grouping records with GROUP BY and counting the rows in each group using COUNT( ). For the examples, assume that catalog recipients are listed in a table named cat_mailing that has the following contents:

mysql> SELECT * FROM cat_mailing;
+-----------+-------------+--------------------------+
| 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 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. (This is a simplification, of course.) The following queries 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 cat_mailing;
  +------+
  | rows |
  +------+
  |    8 |
  +------+

• The number of distinct names:

  mysql> SELECT COUNT(DISTINCT last_name, first_name) AS 'distinct names'
      -> FROM cat_mailing;
  +----------------+
  | distinct names |
  +----------------+
  |              5 |
  +----------------+

• The number of rows containing duplicated names:

  mysql> SELECT COUNT(*) - COUNT(DISTINCT last_name, first_name)
      -> AS 'duplicate names'
      -> FROM cat_mailing;
  +-----------------+
  | duplicate names |
  +-----------------+
  |               3 |
  +-----------------+

• The fraction of the records that contain unique or non-unique names:

  mysql> SELECT COUNT(DISTINCT last_name, first_name) / COUNT(*)
      -> AS 'unique',
      -> 1 - (COUNT(DISTINCT last_name, first_name) / COUNT(*))
      -> AS 'non-unique'
      -> FROM cat_mailing;
  +--------+------------+
  | unique | non-unique |
  +--------+------------+
  |   0.62 |       0.38 |
  +--------+------------+

These queries help you characterize the extent of duplicates, but don't show you which values are duplicated. To see which names are duplicated in the cat_mailing table, use a summary query that displays the non-unique values along with the counts:

mysql> SELECT COUNT(*) AS repetitions, last_name, first_name
    -> FROM cat_mailing
    -> GROUP BY last_name, first_name
    -> HAVING repetitions > 1;
+-------------+-----------+------------+
| repetitions | last_name | first_name |
+-------------+-----------+------------+
|           3 | Baxter    | Wallace    |
|           2 | Pinter    | Marlene    |
+-------------+-----------+------------+

The query 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 this way have the following form:

SELECT COUNT(*), column_list
FROM tbl_name
GROUP BY column_list
HAVING COUNT(*) > 1

It's easy to generate duplicate-finding queries like that within a program, given a table name 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 ($tbl_name, @col_name) = @_;

    return (
        "SELECT COUNT(*)," . join (",", @col_name)
        . "\nFROM $tbl_name"
        . "\nGROUP BY " . join (",", @col_name)
        . "\nHAVING COUNT(*) > 1"
    );
}

make_dup_count_query( ) returns the query as a string. If you invoke it like this:

$str = make_dup_count_query ("cat_mailing", "last_name", "first_name");

The resulting value of $str is:

SELECT COUNT(*),last_name,first_name
FROM cat_mailing
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 out the function (as well as some translations into other languages). Later in this chapter, Recipe 14.7 uses 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 a summary in itself cannot display the entire content of the records that contain the duplicate values. (For example, the summaries shown thus far display counts of duplicated names in the cat_mailing table or the names themselves, but don't show the addresses associated with those names.) To see the original records 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 cat_mailing records that contain duplicated names. The summary is written to a temporary table, which then is joined to the cat_mailing table to produce the records that match those names:

mysql> CREATE TABLE tmp
    -> SELECT COUNT(*) AS count, last_name, first_name
    -> FROM cat_mailing GROUP BY last_name, first_name HAVING count > 1;
mysql> SELECT cat_mailing.*
    -> FROM tmp, cat_mailing
    -> WHERE tmp.last_name = cat_mailing.last_name
    -> AND tmp.first_name = cat_mailing.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       |
+-----------+------------+----------------------+

Duplicate Identification and String Case Sensitivity Non-binary strings that differ in lettercase are considered the same for comparison purposes. To consider them as distinct, use the BINARY keyword to make them case sensitive.