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'?

Solution 1

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

Solution 2

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 ;) )