Adding DISTINCT to a UNION query

Question

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 query but this should be enough to go on.

score 17 · Accepted Answer · edited Oct 28 '16 at 22:17

17

Just remove the ALL. Some flavors allow adding DISTINCT instead of ALL to be more explicit, but that's redundant having that the default is always to filter our duplicates.

MySQL - http://dev.mysql.com/doc/refman/5.0/en/union.html
MSSQL - http://msdn.microsoft.com/en-us/library/ms180026.aspx
ORACLE - https://docs.oracle.com/cd/B28359_01/server.111/b28286/queries004.htm
PostgreSQL - http://www.postgresql.org/docs/8.3/interactive/queries-union.html
etc.

edited Oct 28 '16 at 22:17

Air

8,274
2
53
88

answered Jun 02 '11 at 08:14

Alin Purcaru

43,655
12
77
90

2

"The default behavior for UNION is that duplicate rows are removed from the result. The optional DISTINCT keyword has no effect other than the default because it also specifies duplicate-row removal. With the optional ALL keyword, duplicate-row removal does not occur and the result includes all matching rows from all the SELECT statements." – tofutim Jun 02 '11 at 08:17
I couldn't find the official reference for Oracle, so if anyone knows it please edit the answer or post it in a comment and I'll change it. – Alin Purcaru Jun 02 '11 at 08:21
@tofutim A quote would be good in the answer, but that is from the MySQL manual and he said nothing about using MySQL. – Alin Purcaru Jun 02 '11 at 08:27
I'm using MySQL, forgot to add that, but there is more to the query. See my edits above. The points column throws the uniqueness off. – cdub Jun 02 '11 at 08:30
i still get duplicate id's though. Not sure how to get rid of those. – cdub Jun 02 '11 at 08:36
oh well, i might have an idea – cdub Jun 02 '11 at 08:41
@chris I feared that would be a problem. Wrap it in a `SELECT *` and `GROUP BY Title.id`. Although I'm not sure about the performance of this kind of solution. – Alin Purcaru Jun 02 '11 at 08:42
Ya i might have to eat the performance on that, thx for your help. – cdub Jun 02 '11 at 08:43
@chris: if you get duplicate ids, you may be doing something wrong in the inner queries (possibly with JOINs). Post those queries too. – ypercubeᵀᴹ Jun 02 '11 at 08:54

score 1 · Answer 2 · answered Jun 02 '11 at 08:20

1

Isn't the simple way just get rid of the union and the second part altogether:

SELECT Title.id, Title.title FROM titles as Title HAVING points > 0

since HAVING points > 0 includes anything with HAVING points > 1?

answered Jun 02 '11 at 08:20

Bohemian

412,405
93
575
722

1

That was just a simplistic example. – Alin Purcaru Jun 02 '11 at 08:43

score 1 · Answer 3 · answered Jun 02 '11 at 08:49

You could drop the ALL as others have suggested.

If the two queries use same tables and are different only in the WHERE clause or only in the HAVING clause, you can also use this:

SELECT Title.id, Title.title FROM titles as Title
WHERE (1st query conditions)
   OR (2nd query conditions)

or

SELECT Title.id, Title.title FROM titles as Title
HAVING (1st query conditions)
    OR (2nd query conditions)

Adding DISTINCT to a UNION query

3 Answers3