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?

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 (http://blog.sqlauthority.com)

About these ads

37 thoughts on “SQL SERVER – Simple Puzzle with UNION – Part 3

  1. 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……

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

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

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

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

  6. 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’ ‘

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

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

  9. 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’ “.

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

  11. In accordance with T-SQL rules the column names of result columns in UNION are determined by the first query, so the right is Query 2:
    SELECT *
    FROM (
    SELECT 1 Col
    UNION ALL
    SELECT 2) t

  12. Pingback: SQL SERVER – Simple Puzzle with UNION – Part 4 | Journey to SQL Authority with Pinal Dave

  13. Hi all,
    SELECT *
    FROM (
    SELECT 1 AS Col
    UNION ALL
    SELECT 2) t

    is right answer ,because in this Query the aliases of column must be pass first and then we have to perform the operation . if other than this reason please comment here

  14. Pingback: SQL SERVER – Simple Puzzle with UNION – Part 5 | Journey to SQL Authority with Pinal Dave

  15. without looking, it’s query2. The subquery from query 1 uses a union without columns names, no good. The subquery from query 3 is incorrect since the first query does not have a column name. Query 2 will work since the subquery uses a column name in the first select. that column name will be used as the column name for the union

  16. The column names in the result-set of a UNION ALL are usually equal to the column names in the first SELECT statement in the UNION ALL.

  17. Query 2 will return the result as the column name in the result-set of a UNION ALL should be equal to the column names in the first SELECT statement in the UNION ALL.

  18. Pingback: SQL SERVER – Five Puzzles around UNION – Participate in All Five | Journey to SQL Authority with Pinal Dave

  19. Query2 is CORRECT
    Query 1 & Query 2 are INCORRECT.

    because, IN Query 1 & Query 2 we we dont have any source/coloumn name in the FROM clause.because of no coloumn name specified there it goes into ambiguious state and throws an error.

  20. While performing the Union All, the column names from the first statement among all the statements used for doing Union are taken as the column name of the Final Result. So here in the Subquery part-> in Query 1, we do not have any column names assigned in the Final Result / in Query 3, we do not have any column name assigned in the Final Result / in Query 2, we have column name “col” given to the one and only column of the Subquery.
    Now when the outer Query asks for returning all the columns from the Table (which is a Subquery here), Query 1 and Query 3 fails, as they do not have the column names which is a must in the Select List!!! At the same time, Query 2 gets executed and gives the records from column “col” of the Derived Table.

  21. Hi Pinal i gave two alice name for that second query bt o/p are coming in first alice how is it?

    SELECT *
    FROM (
    SELECT 1 AS Col
    UNION ALL
    SELECT 2 as c) t
    o/p
    Col
    1
    2

  22. Query 2 is Correct.
    Each sub query requires all column names, if constant / expression is present in query as column then alias required for that column. Also UNION query takes column names of first query as column names for result set.

  23. The Query 2 will return the result and the other two queries throw an error.

    In Query 1, the results of an inner query, which forms a table for FROM clause does not have column name to select from in an outer query. Thus it throws an error.

    In Query 3, the result set column names of a UNION are the same as the names in the first select statement, the column names in the other select statements are ignored. Thus here the column names are not considered and resulting in the same error as in Query 1.

  24. Query 2 will execute successfully. Because there is no column specified for other queries in the first SELECT statement of UNION operation.

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

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

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