T-SQL - Aliasing Using "=" Versus "As"

T-SQL - Aliasing using = versus as

‘=’ isn't valid ANSI SQL, so you'll have difficulty should you wish to run your application on a different DBMS.

(It's when ANSI form is used but the optional ‘AS’ is omitted I find the results difficult to read, personally.)

When was aliasing using = versus as introduced and what is the name of that version

This is of a great surprise to me. It seems that the column_alias = expression has always been part of the original syntax of T-SQL.

The AS command was added to T-SQL.80 (2000) to be SQL-92 compatible.

Excerpt of SQL2000_release.pdf page 1459

The AS clause is the syntax defined in the SQL-92 standard for assigning a name to a result set column. This is the preferred
syntax to use in Microsoft® SQL Server™.

column_name AS column_alias

Or

result_column_expression AS derived_column_name

Transact-SQL also supports the following syntax for compatibility with earlier versions of SQL Server:

column_alias = column_name

SQL Server/TSQL : Aliasing a column on table alias in join

In SQL Server, you can use outer apply:

SELECT t1.*, t2.*
FROM [table1] tl JOIN
     [table2] t2
     ON t1.[column1] = t2.[column1] OUTER APPLY
     (VALUES (t1.column1) ) v(doubleAlias)
WHERE doubleAlias = 'value';

What is the point using AS keyword in SQL when aliasing can be done without it?

I think the reason is simple. Consider code such as the following:

select a, b, c, d
. . .

It is very easy to occasionally skip the comma:

select a b, c, d

If you don't use as then this looks like correct code and it can be difficult to figure out. If you always use as for column aliases, then you know it is incorrect.

(T-SQL) Why does this subquery need an alias?

The alias after the subquery (or derived table, if you prefer) is required by SQL Server. It is not only a requirement but a really good idea. In general, column references should be qualified, meaning that they include a table alias. Without an alias, references to columns in the subquery could not be qualified. I think that's a bad thing.

SQL Server is not the only database that requires the alias. MySQL and Postgres (and hence most Postgres-derived databases) do as well. Oracle and SQLite do not. Nor does Google's BigQuery.

I do not know if the alias is an ANSI/ISO requirement. However, I always use one, regardless of the database.

Using calculation with an an aliased column in ORDER BY

The documentation clearly states that:

The column names referenced in the ORDER BY clause must correspond to
either a column or column alias in the select list or to a column
defined in a table specified in the FROM clause without any
ambiguities. If the ORDER BY clause references a column alias from
the select list, the column alias must be used standalone, and not as
a part of some expression in ORDER BY clause:

Technically speaking, your query should work since order by clause is logically evaluated after select clause and it should have access to all expressions declared in select clause. But without looking at having access to the SQL specs I cannot comment whether it is a limitation of SQL Server or the other RDBMS implementing it as a bonus feature.

Anyway, you can use CROSS APPLY as a trick.... it is part of FROM clause so the expressions should be available in all subsequent clauses:

SELECT item
FROM t
CROSS APPLY (SELECT item + '') AS CA(item_for_sort)
ORDER BY item_for_sort

What's the purpose of SQL keyword AS?

There is no difference between both statements above. AS is just a more explicit way of mentioning the alias

---

Related Topics