What does this UN mean?

Question

I wanted to do an insert into with a union, and someone suggested this:

SELECT x INTO ##temp
FROM (SELECT x FROM y UNION ALL SELECT x FROM z) UN

It works, but what is the UN? Unfortunately, Googling for "t-sql un" isn't very helpful :p

Note: I found out that you can just do SELECT x INTO ##temp FROM y UNION ALL SELECT x FROM b but I'm still curious about UN.

EDIT: Ok, so it's an alias, but why is it required to make this work? If I remove it, it won't execute.

Answer 1

It's not a keyword. It's an alias. Any string could have been used there instead of "UN".

In more complete form, it is:

SELECT x INTO ##temp
FROM (SELECT x FROM y UNION ALL SELECT x FROM z) AS UN

---

@Tomalak is correct. In this case, the alias is required. Without the alias, the error is:

Msg 102, Level 15, State 1, Line 2 Incorrect syntax near ')'.

I simplified the query a bit and used the AdventureWorks database:

SELECT * INTO ##temp
FROM (SELECT * FROM Person.Address) 

this receives the error above. In contrast:

SELECT * INTO ##temp
FROM Person.Address

works just fine. An alternative is

;WITH UN AS
(
    SELECT * FROM Person.Address
)
SELECT * INTO ##temp
FROM UN

Answer 2

It is an alias in this context. There is no un T-SQL command. You can then reference your fields with that.

SELECT un.x INTO ##temp
FROM (SELECT x FROM y UNION ALL SELECT x FROM z) UN

Answer 3

In this case, UN is the alias for the result set of the subquery (the (SELECT x FROM y UNION ALL SELECT x FROM z)). You can replace UN with whatever you like as long as it's not going to confuse the parser.

Answer 4

All that does is alias the (SELECT x FROM y UNION ALL SELECT x FROM z) to "UN"

Answer 5

An alias/correlation name gives the result set a temporary name so that it can be referenced elsewhere in the query. This example shows that the alias is required to be able to reference the fields in the sub queries in the select and join clauses. If the sub-queries did not have aliases the server would not know what table to get field 'y' from. The aliases are required to distinguish the two different roles of the sub-queries.

select A.*, B.y
from 
(select id, f, k, y from Table1) A
join
(select id, t, y, s from Table2) B
on A.id = B.id

Answer 6

It is required becasue this is a derived table and a derived tables are required to have a name. Won't this work? I don't see that you need the derived table.

SELECT x 
INTO #Temp
FROM y 
UNION ALL 
SELECT x 
FROM z

I changed to a regular temp table as global ones are usually a bad idea as other connection can affect them. If you really need one then use it, otherwise be very careful of using gloabl temp tables.