Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Combining union and union all


Combining union and union all

Author
Message
R.P.Rozema
R.P.Rozema
Mr or Mrs. 500
Mr or Mrs. 500 (521 reputation)Mr or Mrs. 500 (521 reputation)Mr or Mrs. 500 (521 reputation)Mr or Mrs. 500 (521 reputation)Mr or Mrs. 500 (521 reputation)Mr or Mrs. 500 (521 reputation)Mr or Mrs. 500 (521 reputation)Mr or Mrs. 500 (521 reputation)

Group: General Forum Members
Points: 521 Visits: 1681
Comments posted to this topic are about the item Combining union and union all

Thank you for reading the discussion on my QotD. As you may have noticed, the explanation I gave was wrong, even though 'Option 3' was still the correct answer. Please read on to find the correct explanation.

(oops, I broke the link to the QotD by editing the opening post...)



Posting Data Etiquette - Jeff Moden
Posting Performance Based Questions - Gail Shaw
Hidden RBAR - Jeff Moden
Cross Tabs and Pivots - Jeff Moden
Catch-all queries - Gail Shaw


If you don't have time to do it right, when will you have time to do it over?
baabhu
baabhu
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1417 Visits: 1215
Nice :-)
Koen Verbeeck
Koen Verbeeck
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16544 Visits: 13210
Another great back to basics question. Thanks!



How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at SQLKover.

MCSA SQL Server 2012 - MCSE Business Intelligence
Carlo Romagnano
Carlo Romagnano
Hall of Fame
Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)

Group: General Forum Members
Points: 3632 Visits: 3237
I get it right, but I disagree with explanation:
If at least one 'Union' is used, duplicates will be removed from the entire final result set, no matter where the 'Union' occurs

It depends on precedence or parathesis and this script demonstrates it:

-- return two row
select 1
UNION select 1
UNION ALL select 1



-- return one row
select 1
UNION ALL select 1
UNION select 1




-- return two row
select 1
UNION ALL (select 1
UNION select 1
)


R.P.Rozema
R.P.Rozema
Mr or Mrs. 500
Mr or Mrs. 500 (521 reputation)Mr or Mrs. 500 (521 reputation)Mr or Mrs. 500 (521 reputation)Mr or Mrs. 500 (521 reputation)Mr or Mrs. 500 (521 reputation)Mr or Mrs. 500 (521 reputation)Mr or Mrs. 500 (521 reputation)Mr or Mrs. 500 (521 reputation)

Group: General Forum Members
Points: 521 Visits: 1681
You have left off half of the explanation in your quote. In the next sentence the use of parenthesis to override this behavior is explained:
To preserve duplicates in only a part of the final result set, parenthesis must be used to separate the 'union'-ed statement(s) from the 'union all'-ed statements.


That's exactly what this QotD was meant to test: If at least one union (not all) is used in the statement, all duplicates are removed. If you want to preserve (some) duplicates in the final resultset and at least one "union" (not all) is used in the statement, you will have to use parenthesis to achieve this. The query in the QotD deliberatly did not have such parenthesis to illustrate the behavior. If you follow the link to the article in the answer, you will find under "Example D" an example how to do this.



Posting Data Etiquette - Jeff Moden
Posting Performance Based Questions - Gail Shaw
Hidden RBAR - Jeff Moden
Cross Tabs and Pivots - Jeff Moden
Catch-all queries - Gail Shaw


If you don't have time to do it right, when will you have time to do it over?
Carlo Romagnano
Carlo Romagnano
Hall of Fame
Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)

Group: General Forum Members
Points: 3632 Visits: 3237
R.P.Rozema (3/8/2012)
You have left off half of the explanation in your quote. In the next sentence the use of parenthesis to override this behavior is explained:
To preserve duplicates in only a part of the final result set, parenthesis must be used to separate the 'union'-ed statement(s) from the 'union all'-ed statements.


That's exactly what this QotD was meant to test: If at least one union (not all) is used in the statement, all duplicates are removed. If you want to preserve (some) duplicates in the final resultset and at least one "union" (not all) is used in the statement, you will have to use parenthesis to achieve this. The query in the QotD deliberatly did not have such parenthesis to illustrate the behavior. If you follow the link to the article in the answer, you will find under "Example D" an example how to do this.

No, it depends by the position. Run the code in the previos post.
R.P.Rozema
R.P.Rozema
Mr or Mrs. 500
Mr or Mrs. 500 (521 reputation)Mr or Mrs. 500 (521 reputation)Mr or Mrs. 500 (521 reputation)Mr or Mrs. 500 (521 reputation)Mr or Mrs. 500 (521 reputation)Mr or Mrs. 500 (521 reputation)Mr or Mrs. 500 (521 reputation)Mr or Mrs. 500 (521 reputation)

Group: General Forum Members
Points: 521 Visits: 1681
I see your point. I have to get back on this or maybe someone else sees what's wrong?



Posting Data Etiquette - Jeff Moden
Posting Performance Based Questions - Gail Shaw
Hidden RBAR - Jeff Moden
Cross Tabs and Pivots - Jeff Moden
Catch-all queries - Gail Shaw


If you don't have time to do it right, when will you have time to do it over?
archie flockhart
archie flockhart
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1253 Visits: 1150
Explanation is wrong: you can get multiple rows even if you use UNION in the query.

You can try it using the tables in the question:


select col from #t2
union
select col from #t3
union all
select col from #t1


Results:

col
2
3
1
1
archie flockhart
archie flockhart
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1253 Visits: 1150
It's because of the order of operations: when written this way the UNION happens first and removes duplicates in t2 and t3, then the result is combined with t1 using UNION ALL which allows duplicates.


select col from #t2
union
select col from #t3
union all
select col from #t1


If you change round the UNION and UNION ALL above you get the result set without duplicates, because the last operation performed is a UNION.

select col from #t2
union all
select col from #t3
union
select col from #t1
skanker
skanker
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1019 Visits: 1385
Got it right and thought that I understood - now looking at the other posts I am slightly confused.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search