MYSQL UNION DISTINCT

Question

I have two selects that I'm currently running as a UNION successfully.

(SELECT a.user_id,
          a.updatecontents AS city,
          b.country
   FROM userprofiletemp AS a
   LEFT JOIN userattributes AS b ON a.user_id=b.user_id
   WHERE typeofupdate='city')
UNION DISTINCT
  (SELECT a.user_id,
          c.city,
          c.country
   FROM userverify AS a
   LEFT JOIN userlogin AS b ON a.user_id=b.user_id
   LEFT JOIN userattributes AS c ON a.user_id=c.user_id
   WHERE b.active=1
     AND a.verifycity=0);

The results come back like this:

100 Melbourne Australia
200 NewYork America
300 Tokyo Japan
100 Sydney Australia

The catch is the query will bring duplicate user_id (in this case the 100). The details in the first query take precedent for me and if the user_id is repeated in the second query I don't need it.

Is there a way to get a UNION to be DISTINCT on a column? in this case the user_id? Is there a way to do the above call and not get duplicate user_id's - drop the second. Should I re-write the query differently and not use a UNION. Really want it as one query - I can use to SELECT's and PHP to weed out duplicate if necessary.

thx Adam

user_2743458 · Answer

MySQL UNION produces distinct rows—however, all column values in the row need to be distinct. If you wish to limit the distinction to a single or a few columns, when other columns are not distinct, you can wrap the UNION in a sub-query and GROUP BY the sub-query by the columns you wish to be unique.

Here I wrap the entire UNION in a sub-query, give it an alias, then GROUP BY the desired unique column:

SELECT * FROM (

SELECT a.user_id,a.updatecontents as city,b.country
FROM userprofiletemp AS a
LEFT JOIN userattributes AS b ON a.user_id=b.user_id
WHERE typeofupdate='city'

UNION

SELECT a.user_id,c.city,c.country
FROM userverify AS a
LEFT JOIN userlogin AS b ON a.user_id=b.user_id
LEFT JOIN userattributes AS c ON a.user_id=c.user_id
WHERE b.active=1 AND a.verifycity=0

) aa GROUP BY user_id;

If you have more than one column you'd like to include in the distinction, list them after the GROUP BY: such as GROUP BY user_id, city.

SIDE NOTE: since, in this case, UNION does not provide the desired distinction, there is no benefit to simply using UNION, and apparently "UNION ALL is much faster than UNION", therefore you can use UNION ALL to speed up this query.

MYSQL UNION DISTINCT

3 Answers