Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12345»»»

Combining union and union all Expand / Collapse
Author
Message
Posted Wednesday, March 7, 2012 10:02 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 3:49 AM
Points: 411, Visits: 1,403
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?
Post #1263409
Posted Wednesday, March 7, 2012 10:02 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 12:22 AM
Points: 1,287, Visits: 1,120
Nice
Post #1263410
Posted Wednesday, March 7, 2012 11:53 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:15 PM
Points: 13,731, Visits: 10,692
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 LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1263422
Posted Thursday, March 8, 2012 1:11 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 12:53 AM
Points: 2,528, Visits: 2,402
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
)

Post #1263458
Posted Thursday, March 8, 2012 1:23 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 3:49 AM
Points: 411, Visits: 1,403
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?
Post #1263461
Posted Thursday, March 8, 2012 1:34 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 12:53 AM
Points: 2,528, Visits: 2,402
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.
Post #1263466
Posted Thursday, March 8, 2012 1:38 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 3:49 AM
Points: 411, Visits: 1,403
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?
Post #1263468
Posted Thursday, March 8, 2012 1:44 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, July 9, 2014 2:52 AM
Points: 1,153, Visits: 1,047
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
Post #1263470
Posted Thursday, March 8, 2012 1:47 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, July 9, 2014 2:52 AM
Points: 1,153, Visits: 1,047
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
Post #1263473
Posted Thursday, March 8, 2012 1:48 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 6:15 AM
Points: 900, Visits: 1,247
Got it right and thought that I understood - now looking at the other posts I am slightly confused.
Post #1263474
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse