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?

Answer : SQL SERVER – Simple Puzzle Using Union and Union All – Answer

Reference : Pinal Dave (http://blog.SQLAuthority.com)

About these ads

14 thoughts on “SQL SERVER – Simple Puzzle Using Union and Union All

  1. 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 ? :-)

    Like

  2. 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.

    Like

  3. Pingback: SQL SERVER - Simple Puzzle Using Union and Union All - Answer Journey to SQL Authority with Pinal Dave

    • 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.

      Like

  4. ^
    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

    Like

  5. 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

    Like

  6. Pingback: SQL SERVER – Weekly Series – Memory Lane – #022 | SQL Server Journey with SQL Authority

  7. Pingback: SQL SERVER – UNION ALL and UNION are Different Operation | SQL Server Journey with SQL Authority

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

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s