Sean Lange (5/16/2013)
John Mitchell-245523 (5/16/2013)
Phil Parkin (5/16/2013)
lnardozi 61862 (5/16/2013)
Just for completeness sake, you never use more than one UNION, which is the last one. All the rest should be UNION ALL.Why not?
I suppose because UNION eliminates duplicates you only need to do it once - as long as you make sure that UNION is the last one evaluated. I don't know whether the query optimizer is smart enough to do that implicitly if you use more than one UNION - so it may or may not make a difference from a performance point of view.
John
But that is only good if you want to eliminate ALL duplicates. It is very possible that you have 3 tables and in the first 2 tables you want duplicates but if the same value shows up in the third table you want only distinct values. Other times maybe you want to include all duplicates.
The blanket statement that "you never use more than one UNION, which is the last one. All the rest should be UNION ALL." is just completely not true. There are time when that is correct but not always.
I'm not sure I understand. Those duplicates from the first two tables will be eliminated by the UNION operator. Try this:
SELECT * FROM (VALUES (1), (1), (2)) x(A)
UNION ALL
SELECT * FROM (VALUES (2), (3), (3)) y(B)
UNION
SELECT * FROM (VALUES (4)) z(C)
John