Recipe 14.5. Eliminating Duplicates from a Self-Join Result
ProblemSelf-joins often produce rows that are "near" duplicatesthat is, rows that contain the same values but in different orders. Because of this, SELECT DISTINCT will not eliminate the duplicates. SolutionSelect 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. DiscussionSelf-joins can produce rows that are duplicates in the sense that they contain the same values, yet are not identical. Consider the following statement (originally seen in Section 12.3), which uses a self-join to find all pairs of states that joined the Union in the same year: mysql> SELECT YEAR(s1.statehood) AS year, -> s1.name AS name1, s1.statehood AS statehood1, -> s2.name AS name2, s2.statehood AS statehood2 -> FROM states AS s1 INNER JOIN states AS s2 -> ON YEAR(s1.statehood) = YEAR(s2.statehood) AND s1.name != s2.name -> ORDER BY year, s1.name, s2.name; +------+----------------+------------+----------------+------------+ | year | name1 | statehood1 | name2 | statehood2 | +------+----------------+------------+----------------+------------+ | 1787 | Delaware | 1787-12-07 | New Jersey | 1787-12-18 | | 1787 | Delaware | 1787-12-07 | Pennsylvania | 1787-12-12 | | 1787 | New Jersey | 1787-12-18 | Delaware | 1787-12-07 | | 1787 | New Jersey | 1787-12-18 | Pennsylvania | 1787-12-12 | | 1787 | Pennsylvania | 1787-12-12 | Delaware | 1787-12-07 | | 1787 | Pennsylvania | 1787-12-12 | New Jersey | 1787-12-18 | ... | 1912 | Arizona | 1912-02-14 | New Mexico | 1912-01-06 | | 1912 | New Mexico | 1912-01-06 | Arizona | 1912-02-14 | | 1959 | Alaska | 1959-01-03 | Hawaii | 1959-08-21 | | 1959 | Hawaii | 1959-08-21 | Alaska | 1959-01-03 | +------+----------------+------------+----------------+------------+ The condition in the ON clause that requires state pair names not to be identical eliminates the trivially duplicate 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 statement. 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, in which the expressions display state names in lexical order within each row: mysql> SELECT YEAR(s1.statehood) AS year, -> IF(s1.name<s2.name,s1.name,s2.name) AS name1, -> IF(s1.name<s2.name,s1.statehood,s2.statehood) AS statehood1, -> IF(s1.name<s2.name,s2.name,s1.name) AS name2, -> IF(s1.name<s2.name,s2.statehood,s1.statehood) AS statehood2 -> FROM states AS s1 INNER JOIN states AS s2 -> ON YEAR(s1.statehood) = YEAR(s2.statehood) AND s1.name != s2.name -> ORDER BY year, name1, name2; +------+----------------+------------+----------------+------------+ | year | name1 | statehood1 | name2 | statehood2 | +------+----------------+------------+----------------+------------+ | 1787 | Delaware | 1787-12-07 | New Jersey | 1787-12-18 | | 1787 | Delaware | 1787-12-07 | New Jersey | 1787-12-18 | | 1787 | Delaware | 1787-12-07 | Pennsylvania | 1787-12-12 | | 1787 | Delaware | 1787-12-07 | Pennsylvania | 1787-12-12 | | 1787 | New Jersey | 1787-12-18 | Pennsylvania | 1787-12-12 | | 1787 | New Jersey | 1787-12-18 | Pennsylvania | 1787-12-12 | ... | 1912 | Arizona | 1912-02-14 | New Mexico | 1912-01-06 | | 1912 | Arizona | 1912-02-14 | New Mexico | 1912-01-06 | | 1959 | Alaska | 1959-01-03 | Hawaii | 1959-08-21 | | 1959 | Alaska | 1959-01-03 | Hawaii | 1959-08-21 | +------+----------------+------------+----------------+------------+ Duplicate rows are still present in the output, but now duplicate pairs are identical and you can eliminate the extra copies by adding DISTINCT to the statement: mysql> SELECT DISTINCT YEAR(s1.statehood) AS year, -> IF(s1.name<s2.name,s1.name,s2.name) AS name1, -> IF(s1.name<s2.name,s1.statehood,s2.statehood) AS statehood1, -> IF(s1.name<s2.name,s2.name,s1.name) AS name2, -> IF(s1.name<s2.name,s2.statehood,s1.statehood) AS statehood2 -> FROM states AS s1 INNER JOIN states AS s2 -> ON YEAR(s1.statehood) = YEAR(s2.statehood) AND s1.name != s2.name -> ORDER BY year, name1, name2; +------+----------------+------------+----------------+------------+ | year | name1 | statehood1 | name2 | statehood2 | +------+----------------+------------+----------------+------------+ | 1787 | Delaware | 1787-12-07 | New Jersey | 1787-12-18 | | 1787 | Delaware | 1787-12-07 | Pennsylvania | 1787-12-12 | | 1787 | New Jersey | 1787-12-18 | Pennsylvania | 1787-12-12 | ... | 1912 | Arizona | 1912-02-14 | New Mexico | 1912-01-06 | | 1959 | Alaska | 1959-01-03 | Hawaii | 1959-08-21 | +------+----------------+------------+----------------+------------+ An alternative approach to removing nonidentical 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 in which the first state name is lexically less than the second automatically eliminates rows whose names appear in the other order: [*]
mysql> SELECT YEAR(s1.statehood) AS year, -> IF(s1.name<s2.name,s1.name,s2.name) AS name1, -> IF(s1.name<s2.name,s1.statehood,s2.statehood) AS statehood1, -> IF(s1.name<s2.name,s2.name,s1.name) AS name2, -> IF(s1.name<s2.name,s2.statehood,s1.statehood) AS statehood2 -> FROM states AS s1 INNER JOIN states AS s2 -> ON YEAR(s1.statehood) = YEAR(s2.statehood) AND s1.name < s2.name -> ORDER BY year, name1, name2; +------+----------------+------------+----------------+------------+ | year | name1 | statehood1 | name2 | statehood2 | +------+----------------+------------+----------------+------------+ | 1787 | Delaware | 1787-12-07 | New Jersey | 1787-12-18 | | 1787 | Delaware | 1787-12-07 | Pennsylvania | 1787-12-12 | | 1787 | New Jersey | 1787-12-18 | Pennsylvania | 1787-12-12 | ... | 1912 | Arizona | 1912-02-14 | New Mexico | 1912-01-06 | | 1959 | Alaska | 1959-01-03 | Hawaii | 1959-08-21 | +------+----------------+------------+----------------+------------+ |
