In SQL / MySQL, can a Left Outer Join be used to find out the duplicates when there is no Primary ID
- by Jian Lin
I would like to try using Outer Join to find out duplicates in a table:
If a table has Primary Index ID, then the following outer join can find out the duplicate names:
mysql> select * from gifts;
+--------+------------+-----------------+---------------------+
| giftID | name | filename | effectiveTime |
+--------+------------+-----------------+---------------------+
| 2 | teddy bear | bear.jpg | 2010-04-24 04:36:03 |
| 3 | coffee | coffee123.jpg | 2010-04-24 05:10:43 |
| 6 | beer | beer_glass.png | 2010-04-24 05:18:12 |
| 10 | heart | heart_shape.jpg | 2010-04-24 05:11:29 |
| 11 | ice tea | icetea.jpg | 2010-04-24 05:19:53 |
| 12 | cash | cash.png | 2010-04-24 05:27:44 |
| 13 | chocolate | choco.jpg | 2010-04-25 04:04:31 |
| 14 | coffee | latte.jpg | 2010-04-27 05:49:52 |
| 15 | coffee | espresso.jpg | 2010-04-27 06:03:03 |
+--------+------------+-----------------+---------------------+
9 rows in set (0.00 sec)
mysql> select * from gifts g1 LEFT JOIN (select * from gifts group by name) g2
on g1.giftID = g2.giftID where g2.giftID IS NULL;
+--------+--------+--------------+---------------------+--------+------+----------+---------------+
| giftID | name | filename | effectiveTime | giftID | name | filename | effectiveTime |
+--------+--------+--------------+---------------------+--------+------+----------+---------------+
| 14 | coffee | latte.jpg | 2010-04-27 05:49:52 | NULL | NULL | NULL | NULL |
| 15 | coffee | espresso.jpg | 2010-04-27 06:03:03 | NULL | NULL | NULL | NULL |
+--------+--------+--------------+---------------------+--------+------+----------+---------------+
2 rows in set (0.00 sec)
But what if the table doesn't have a Primary Index ID, then can an outer join still be used to find out duplicates?
