Alias names in union / union all query - SQL Server

Question

I have a simple sql statements as below

CASE 1:

select 1 as a 
union  
select 2 as a

Output: This case is working as expected

CASE 2:

select 1 as a 
union  
select 2 as b

Output: Though the alias is 'b' in my second select, it still shows the alias 'a'.

Why cant it take the alias from the second select statement?

How can we make sql to choose the alias from the second select query?

CASE 3:

select 1 
union  
select 2 as b

Output: Even though my first select statement above does not have any alias name but the second one still have, why the result still shows 'No column name'?

By default when you union data in sql it chooses the column names from the first query. — Simon, Feb 27 '17 at 16:45
The alias system in a UNION query only works for the first query in the UNION stack everything else (other queries) is ignored — Jorge Campos, Feb 27 '17 at 16:45
Using some basic reasoning it appears it always goes by the first one. — RSon1234, Feb 27 '17 at 16:45
Think about it , you are only creating 1 column - it has to get its name from somewhere and the rule is that somewhere is the first sql statement. — P.Salmon, Feb 27 '17 at 16:49
You haven't actually explained why you're asking this? Since you somehow want it to come from the last result set, is there an actual reasoning behind that? — James Z, Feb 27 '17 at 17:06

score 5 · Answer 1 · answered Feb 27 '17 at 16:57

Lets try to teach something useful here. It is not just It's because that's how it is. There is a pattern definition that stipulate the rules for the SQL language and it is called SQL ANSI. You can see a timeline of this definition here: Database Administration - ANSI SQL Standards and Guidelines

The reason behind this definition is simple to understand. Since a UNION operation transform the result of two queries into one, some rules must be applied like the definition of the fields name, the types of the fields (in order they are select) and some others.

The alias part works just for the first one because there is no way for the database to identify which column would be the right one in a union operation as you will get one row per result:

 select 1 as a
 UNION
 select 2

This will result in:

 a
 1
 2

Since it is showed as ROWS how the database would work if it name each column for each SQL in the UNION stack?

 -a
  1
 -b
  2

That's why the rule of the first query alias is applied.

The SQL ANSI document is not free although if you dig enough you may find earlier versions of it in PDF. Good luck with that :) (hint: I have an answer in my profile with a working link ;) )

Just to make it easier to find: http://stackoverflow.com/a/34355862/460557 — Jorge Campos, Feb 27 '17 at 17:07

score 2 · Accepted Answer · answered Feb 27 '17 at 16:44

You could wrap everything around a single SELECT and alias that column with what you want.

select a as [b] -- just alias it here with whatever you want
from (
    select 1 as a 
    union  
    select 2 as b
    ...) result_set

But as @Will said, it is what it is, can't change that.

OR:

Just make sure that you use the query with the "alias" that you want at the top and UNION that with a single SELECT statement that contains other queries / values you have.

select 2 as b

union

select a
from (
    select 1 as a
    union
    select 3 as c
    union
    ... ) result_set

Alias names in union / union all query - SQL Server

2 Answers2