SELECT UNION as DISTINCT

mysql union distinct
user1124535 picture user1124535 · Jan 9, 2012 · Viewed 45.7k times · Source

How do I perform a DISTINCT operation on a single column after a UNION is performed?

T1
--
ID Value 
1  1
2  2
3  3

T2
--
ID Value
1  2
4  4
5  5


I am trying to return the table:

ID Value
1  1
2  2
3  3
4  4
5  5

I tried:

SELECT DISTINCT ID, Value 
FROM (SELECT*FROM T1 UNION SELECT*FROM T2) AS T3

This does not seem to work.

Answer

Bohemian picture Bohemian · Jan 9, 2012

Why are you using a sub-query? This will work:

SELECT * FROM T1
UNION
SELECT * FROM T2

UNION removes duplicates. (UNION ALL does not)

Related questions

MYSQL UNION DISTINCT

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=…

Read More
Adding DISTINCT to a UNION query

How do I get distinct title.id's from this: SELECT Title.id, Title.title FROM titles as Title HAVING points > 0 UNION ALL SELECT Title.id, Title.title FROM titles as Title HAVING points > 1 There is more to the …

Read More
Using union and order by clause in mysql

I want to use order by with union in mysql query. I am fetching different types of record based on different criteria from a table based on distance for a search on my site. The first select query returns data …

Read More