I often get request to write puzzles using SQL Server. Today, I am presenting one very simple but very interesting puzzle.
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
Reference : Pinal Dave (https://blog.sqlauthority.com)
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