MySQL Cookbook

14.6 Eliminating Duplicates from a Self-Join Result

14.6.1 Problem

Self-joins often produce rows that are "near" duplicates—that is, rows that contain the same values but in different orders. Because of this, SELECT DISTINCT will not eliminate the duplicates.

14.6.2 Solution

Select column values in a specific order within rows to make rows with duplicate sets of values identical. Then you can use SELECT DISTINCT to remove duplicates. Alternatively, retrieve rows in such a way that near-duplicates are not even selected.

14.6.3 Discussion

Self-joins can produce rows that are duplicates in the sense that they contain the same values, yet are not identical. Consider the following query, which uses a self-join to find all pairs of states that joined the Union in the same year:

mysql> SELECT YEAR(s2.statehood) AS year, s1.name, s2.name
    -> FROM states AS s1, states AS s2
    -> WHERE YEAR(s1.statehood) = YEAR(s2.statehood)
    -> AND s1.name != s2.name
    -> ORDER BY year, s1.name, s2.name;
+------+----------------+----------------+
| year | name           | name           |
+------+----------------+----------------+
| 1787 | Delaware       | New Jersey     |
| 1787 | Delaware       | Pennsylvania   |
| 1787 | New Jersey     | Delaware       |
| 1787 | New Jersey     | Pennsylvania   |
| 1787 | Pennsylvania   | Delaware       |
| 1787 | Pennsylvania   | New Jersey     |
...
| 1912 | Arizona        | New Mexico     |
| 1912 | New Mexico     | Arizona        |
| 1959 | Alaska         | Hawaii         |
| 1959 | Hawaii         | Alaska         |
+------+----------------+----------------+

The condition in the WHERE clause that requires state pair names not to be identical eliminates the trivially true rows showing that each state joined the Union in the same year as itself. But each remaining pair of states still appears twice. For example, there is one row that lists Delaware and New Jersey, and another that lists New Jersey and Delaware. Each such pair of rows may be considered as effective duplicates because they contain the same values. However, because the values are not listed in the same order within the rows, they are not identical and you can't get rid of the duplicates by adding DISTINCT to the query.

One way to solve this problem is to make sure that state names are always listed in a specific order within a row. This can be done by selecting the names with a pair of expressions that place the lesser value first in the output column list:

IF(val1<val2,val1,val2) AS lesser_value,
IF(val1<val2,val2,val1) AS greater_value

Applying this technique to the state-pairs query yields the following result, where the expressions display state names in lexical order within each row:

mysql> SELECT YEAR(s2.statehood) AS year,
    -> IF(s1.name<s2.name,s1.name,s2.name) AS state1,
    -> IF(s1.name<s2.name,s2.name,s1.name) AS state2
    -> FROM states AS s1, states AS s2
    -> WHERE YEAR(s1.statehood) = YEAR(s2.statehood)
    -> AND s1.name != s2.name
    -> ORDER BY year, state1, state2;
+------+----------------+----------------+
| year | state1         | state2         |
+------+----------------+----------------+
| 1787 | Delaware       | New Jersey     |
| 1787 | Delaware       | New Jersey     |
| 1787 | Delaware       | Pennsylvania   |
| 1787 | Delaware       | Pennsylvania   |
| 1787 | New Jersey     | Pennsylvania   |
| 1787 | New Jersey     | Pennsylvania   |
...
| 1912 | Arizona        | New Mexico     |
| 1912 | Arizona        | New Mexico     |
| 1959 | Alaska         | Hawaii         |
| 1959 | Alaska         | Hawaii         |
+------+----------------+----------------+

Duplicate rows are still present in the output, but now duplicate pairs are identical and the extra copies can be eliminated by adding DISTINCT to the query:

mysql> SELECT DISTINCT YEAR(s2.statehood) AS year,
    -> IF(s1.name<s2.name,s1.name,s2.name) AS state1,
    -> IF(s1.name<s2.name,s2.name,s1.name) AS state2
    -> FROM states AS s1, states AS s2
    -> WHERE YEAR(s1.statehood) = YEAR(s2.statehood)
    -> AND s1.name != s2.name
    -> ORDER BY year, state1, state2;
+------+----------------+----------------+
| year | state1         | state2         |
+------+----------------+----------------+
| 1787 | Delaware       | New Jersey     |
| 1787 | Delaware       | Pennsylvania   |
| 1787 | New Jersey     | Pennsylvania   |
...
| 1912 | Arizona        | New Mexico     |
| 1959 | Alaska         | Hawaii         |
+------+----------------+----------------+

An alternative approach to removing non-identical duplicates relies not so much on detecting and eliminating them as on selecting rows in such a way that only one row from each pair ever appears in the query result. This makes it unnecessary to reorder values within output rows or to use DISTINCT. For the state-pairs query, selecting only those rows where the first state name is lexically less than the second automatically eliminates rows where the names appear in the other order:^[1]

^[1] The same constraint also eliminates those rows where the state names are identical.

mysql> SELECT YEAR(s2.statehood) AS year, s1.name, s2.name
    -> FROM states AS s1, states AS s2
    -> WHERE YEAR(s1.statehood) = YEAR(s2.statehood)
    -> AND s1.name < s2.name
    -> ORDER BY year, s1.name, s2.name;
+------+----------------+----------------+
| year | name           | name           |
+------+----------------+----------------+
| 1787 | Delaware       | New Jersey     |
| 1787 | Delaware       | Pennsylvania   |
| 1787 | New Jersey     | Pennsylvania   |
...
| 1912 | Arizona        | New Mexico     |
| 1959 | Alaska         | Hawaii         |
+------+----------------+----------------+