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 (https://blog.sqlauthority.com)
34 Comments. Leave new
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
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
Query 2 will give proper result. Cos the union will take first Column name as default wen second is empty . So SELECT * FROM (— Column will be A–) T .
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
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.
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.
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.
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.
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
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.
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.
Query 2 will execute successfully. Because there is no column specified for other queries in the first SELECT statement of UNION operation.
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.