SQL SERVER – Simple Puzzle with UNION – Part 3

Earlier last week I had two simple puzzles related to UNION clause and the response to those puzzles have been amazing. Lots of email I have received that people wants me to post such basics puzzle again. Well here is one more puzzle which uses UNION and tests your basic knowledge. However, before you continue for today’s puzzle, I suggest you to read earlier two puzzles. If you have not participated in this earlier two puzzle. Please go ahead, there is no prize for winning besides satisfaction you can get when you get the basics correct.

Now let us see today’s puzzle. There are three different script in this puzzle. Out of following three T-SQL script only one of the script is correct and other two are incorrect. Try to answer today’s puzzle without executing the queries in the SQL Server Management studio. Here is the question for you?

Q) Which of the following query is valid query and WHY?

Query 1

SELECT *
FROM (
SELECT 1
UNION ALL
SELECT 2) t

Query 2

SELECT *
FROM (
SELECT 1 AS Col
UNION ALL
SELECT 2) t

Query 3

SELECT *
FROM (
SELECT 1
UNION ALL
SELECT 2 AS Col) t

Only one of the above query will return following result – other queries will throw an error. Why?

SQL SERVER - Simple Puzzle with UNION - Part 3 puzzunion-33

Please leave your answer as a comment. Next week, I will post answer to all the puzzles and will publish all the valid answers with due credit.

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

,
Previous Post
SQL SERVER – Download SQL Server Developer Edition 2012 for USD 60
Next Post
SQL SERVER – Simple Puzzle with UNION – Part 4

Related Posts

34 Comments. Leave new

  • I’m guessing query 2 should be

    SELECT *
    FROM (
    SELECT 1 Col
    UNION ALL
    SELECT 2) t

    Reply
  • i think there was a typo in either 1st or 2nd query (both queries are same….)
    the answer should be
    SELECT *
    FROM(SELECT 1 AS Col
    UNION ALL
    SELECT 2 ) t

    note: remaining queries will throw error because of no column name exist in the inner query……

    Reply
  • Hi sir,
    Query 1 and 2 are the same ??

    Reply
  • First, JimBob is correct. Second Jimbob’s query works as derived table needs a named column to work. A set operator in sql takes column name from first statement that is evaluated, so takes Col as column name for the union, which is subsequently used by the derived table.

    Reply
  • Hello Sir, the first two querys (query1 and query2) are same no changes in that. In the third query the column name ‘COL’ should come after the ‘SELECT 1’. If we run the third query we will receive the error like query1 and query 2.

    Reply
  • Hello Sir, the first two querys (query1 and query2) are same no changes in that. In the third query the column name ‘COL’ should come after the ‘SELECT 1’. If we run the third query we will receive the same error like for query1 and query 2.

    Reply
  • Alok Kumar Seth
    September 9, 2013 9:28 am

    Query 3

    Reply
  • Query 1 and Query 2 are same.

    I think, 2nd should be
    SELECT *
    FROM (
    SELECT 1 Col
    UNION ALL
    SELECT 2) t

    It will give an error in
    – Query1 because there is no reference for column.
    – Query3 because According to rule, if you want to make alias as column name in UNION then it must define in 1st query.

    Reply
  • SELECT *
    FROM (
    SELECT 1 as Col
    UNION ALL
    SELECT 2 ) t
    The query takes the names of the first SELECT statement to be the names of the column,so alias should be specified.If not mentioned it will throw the error
    ‘No column name was specified for column 1 of ‘t’ ‘

    Reply
  • Hi
    All the queries are wrong. First two queries does not have any column name specified and the last one has the column name but it should be the first in union all. The right query is as below

    SELECT *
    FROM (
    SELECT 1 AS Col
    UNION ALL
    SELECT 2 ) t

    Reply
  • “select *” query need well defined column names
    &
    “union query” always takes name of column from first query.

    Reply
  • Following are the 2 correct answers. Which is not present as a option.
    1. SELECT *
    FROM (
    SELECT 1 AS Col
    UNION ALL
    SELECT 2 AS Col) t

    2. SELECT *
    FROM (
    SELECT 1 AS Col
    UNION ALL
    SELECT 2 ) t

    My preferred option is 1.
    Also I observed that option 1 & 2, you have mentioned are same.

    Reply
  • Hello Sir, Query 1 and 2 are same …

    SELECT *
    FROM (
    SELECT 1 as Col
    UNION ALL
    SELECT 2 ) t

    there is no reference for column in Query 1 and Alias as Column name in Union should define in 1st Query ,if not it’ll throw error “No column name was specified for column 1 of ‘t’ “.

    Reply
  • Query 2 provide desired result.

    Reply
  • Query 2 provide desired result.
    WHY: Query parser search column name first and then perform union.

    Reply
  • I think also the second query should be as below–

    SELECT *
    FROM (
    SELECT 1 col
    UNION ALL
    SELECT 2) t

    this will only return the result because in UNION ALL query column name is taken from the 1st statement and the same is used by all following statements.

    1st query : no col name or aliasing present
    3rd query : aliasing/col name is present in the second statement but not in the 1st one.

    Reply
  • Query 2 is correct

    Reply
  • 2 is correct answer

    Reply
  • 2 is the correct answer. For a union query, the 1st SELECT statement must contain the column names.

    Reply
  • shivendra kumar yadav
    September 9, 2013 9:59 pm

    query 2nd is correct because column name is mandatory in case of derived tables and in UNION ALL clause if we specify column in first set than it would be applicable for all sets.

    Reply

Leave a Reply

Menu