I often get request to write puzzles using SQL Server. Today, I am presenting one very simple but very interesting puzzle. The puzzle mixes UNION and UNION ALL in two tiny scripts. It is a simple puzzle using UNION and UNION ALL, so give it a try.
What will be the output of following two SQL Scripts. First try to answer without running this two script in Query Editor.
Script 1
SELECT 1
UNION ALL
(SELECT 1
UNION
SELECT 2)
GO
Script 2
(SELECT 1
UNION ALL
SELECT 1)
UNION
SELECT 2
GO
Hint : This puzzle is based on my previous article SQL SERVER – Union vs. Union All – Which is better for performance?
Answer : SQL SERVER – Simple Puzzle Using Union and Union All – Answer
How to Solve a Simple Puzzle Using Union Without Running It
I will not give away the answer here, but I will share how I work these out on paper. There are only two rules to remember. UNION removes duplicate rows from the combined result. UNION ALL keeps every row, duplicates included.
The third thing to know is the order. When a query mixes the two without parentheses, SQL Server reads them from left to right. Parentheses change that order, just as they do in math. So the trick is to solve the part inside the brackets first, write down the rows you get, and only then apply the operator outside.
Here is how I would approach each script:
- Find the part in parentheses and list its rows on paper.
- Apply the outer operator to that list and to the remaining SELECT.
- Remember that a UNION at the last step removes duplicates from everything that came before it, not only from its own two inputs.
- Count the rows you expect, then run the script and compare.
This matters in real code too. The same mistake can make a report show duplicate rows, or quietly drop rows you wanted to keep. Also keep in mind that UNION ALL is faster, because it skips the duplicate check, so use UNION only when you really want duplicates removed. If you are not sure, run both versions and compare the row counts. Try the puzzle before you look at the answer, because working it out yourself is where the learning happens.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
Discover more from SQL Authority with Pinal Dave
Subscribe to get the latest posts sent to your email.
12 Comments. Leave new
I forgot to mention that i got the first one right without running it… the second one is helped by Query Analyzer… can’t claim a prize then can I ? :-)
Answer is
1
1
2
Union All is faster than Union coz… by default Union apply
DISTINCT to remove duplicate record in set
And Union All show all rows including duplicates.
#1
1
1
2
#2
1
2
I like UNION ALL :)
can u explain how it will produce the result
1
2
thanks,
ashok
Ashok.. In Second query You can see that they have taken first part query with ‘Union’ with second part.
So If first part of Query will Given
1
2
Union
1
Then result will be
1
2.
^
First it will do the UNION ALL so the result set would be
1
1
Next it would do a UNION (so it would do a DISTINCT) so the final result set would be.
1
2
Hope it helps,
geoff
Script 1#
———-
1
1
2
Script 2#
————
1
2
Script 1
1
1
2
Script 2
1
2
The first script would result in:
1
1
2
this is because the union part would run first giving:
1
2
then the union all would run giving you:
1
1
2
The second script woudl result in:
1
2
this is because the union all does not eliminate duplicates and results in:
1
1
then the union part runs which eliminates duplicates and gives
1
2
Both the cases Answer is
1
2
1st :- 1
1
2
2nd:-1
2
I answered it without running the script.. cause of
(SQL SERVER – Difference Between Union vs. Union All – Optimal Performance Comparison)
one of ur blog post
Thanx :-)
It should be noted that union and union all can not be used to combine sequence values. Refer this post for more information