{"id":2086,"date":"2014-12-11T14:33:53","date_gmt":"2014-12-11T22:33:53","guid":{"rendered":"http:\/\/www.wellgolly.com\/?p=2086"},"modified":"2015-04-07T08:46:32","modified_gmt":"2015-04-07T15:46:32","slug":"mysql-union","status":"publish","type":"post","link":"https:\/\/www.wellgolly.com\/?p=2086","title":{"rendered":"mySQL: UNION"},"content":{"rendered":"

I wanted to find all of the emails in my customer database that were duplicates. This could be because several people at the same business share an address or because someone is in the database twice with slightly different spellings of their name e.g. Kathy and Kathleen, or with and without a middle initial, etc. <\/p>\n

Before I started I set all of the email addresses that were empty strings to NULL, since joins don\u2019t match on NULL but do on empty strings.<\/p>\n

Then on the first try I used this:
\n

\nSELECT t1.name, t1.email, t2.name, t2.email\nFROM customers AS t1\nINNER JOIN customers AS t2 \nON t1.email = t2.email\nWHERE t1.name <> t2.name\n<\/code><\/pre>
\nOr in WHERE notation.
\n
\nSELECT t1.name, t1.email, t2.name, t2.email\nFROM customers AS t1, customers AS t2 \nWHERE t1.email = t2.email\nAND t1.name <> t2.name\n<\/code><\/pre><\/p>\n
I got 384 records, but I got a lot of duplicates where t1.name and t2.name are reversed. i.e.
\n
\nHelen Jaffa     hjaffa@ino.net   Helen A Jaffa   hjaffa@ino.net\nHelen A Jaffa   hjaffa@ino.net   Helen Jaffa     hjaffa@ino.net\n<\/code><\/pre><\/p>\n
To eliminate duplicates I used a UNION and two OUTER JOINS
\n
\nSELECT t1.email, t1.name\nFROM customers AS t1\nLEFT OUTER JOIN customers AS t2 ON t1.email = t2.email\nWHERE t1.name <> t2.name\nUNION\nSELECT t2.email, t2.name\nFROM customers AS t2\nLEFT OUTER JOIN customers AS t1 ON t1.email = t2.email\nWHERE t1.name <> t2.name\n<\/code><\/pre><\/p>\n
This yielded 343 records and no duplicates.<\/p>\n","protected":false},"excerpt":{"rendered":"
I wanted to find all of the emails in my customer database that were duplicates. This could be because several people at the same business share an address or because someone is in the database twice with slightly different spellings of their name e.g. Kathy and Kathleen, or with and without a middle initial, etc. … Continue reading mySQL: UNION<\/span><\/a><\/p>\n","protected":false},"author":6,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[27],"tags":[],"class_list":["post-2086","post","type-post","status-publish","format-standard","hentry","category-mysql"],"_links":{"self":[{"href":"https:\/\/www.wellgolly.com\/index.php?rest_route=\/wp\/v2\/posts\/2086","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.wellgolly.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.wellgolly.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.wellgolly.com\/index.php?rest_route=\/wp\/v2\/users\/6"}],"replies":[{"embeddable":true,"href":"https:\/\/www.wellgolly.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=2086"}],"version-history":[{"count":0,"href":"https:\/\/www.wellgolly.com\/index.php?rest_route=\/wp\/v2\/posts\/2086\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.wellgolly.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=2086"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.wellgolly.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=2086"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.wellgolly.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=2086"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}