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.
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.
Self-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 Comparing a Table to Itself), 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:
[18]
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 | +------+----------------+------------+----------------+------------+
3.15.214.155