CODEWITHSUNDEEP
sql-serverselectsql-server-2012
Dave
Dave

Reputation: 1277

Basic SQL union all as tmp

New to SQL and had a general question regarding a pretty basic union all statement. I'm looking at a piece of code that looks like this:

select * 
into ConsolidatedTable 
from 
    (select * from Table1
     union all
     select * from Table2
     union all
     select * from Table3) as tmp;

Is it necessary to have 'as tmp'? If so, why?

Thanks!

Upvotes: 2

Views: 1466

Answers (2)

spencer7593
spencer7593

Reputation: 108410

For SQL Server, yes, an alias is required for the inline view. If the alias is omitted, SQL Server will throw an error.

The AS keyword before the alias is optional, and is not required.

(My personal preference is to omit the AS keyword for table/view aliases, and to always include the AS keyword for column aliases.)

The AS keyword for a table/view alias is optional in SQL Server. (It's not part of the ANSI SQL standard.) Some databases (such as Oracle) do not accept the AS keyword before a table or view alias, and will throw an error if it's included.

Some databases, such as MySQL and SQL Server require that an alias be assigned to an inline view. Databases that do not require an alias on an inline view (e.g. Oracle) allow for an alias, and having an alias assigned allows us qualify column references in the outer query. Since qualifying column references is our normal pattern, assigning an alias to an inline view is also our normal pattern, even for databases that don't require it.

Note that the assignment of an alias is unrelated to the usage of UNION ALL in the inline view query.

Upvotes: 2

Mihai Ovidiu Drăgoi
Mihai Ovidiu Drăgoi

Reputation: 1317

tmp is called an alias. It is mandatory because otherwise the columns in the inline view (all code encased in parantheses) would become "unreachable" in the rest of the query, as in you would not be able to reference them.

It could have any name you wish, it's only used for further reference.

And, to answer your last question, is it necessary to have 'as tmp'? - since this is sql server, you can directly write the alias name without the 'as' - so ) tmp would suffice.

Upvotes: 1

Related Questions