# SQL SERVER – Simple Puzzle Using Union and Union All

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?

Reference : Pinal Dave (https://blog.sqlauthority.com)

## SQL SERVER – List All Dates Between Start and End Date

• 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 ? :-)

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 :-)