Search code examples
sqlcolumn-alias

Using equals in a select clause to set column aliases

Today I saw someone using this syntax (A) to set column aliases in a sql select.

A

Select [ColumnAlias] = Column1 FROM Table

B

Normally I use

Select Column1 AS [ColumnAlias] FROM Table

I've used = to set local variables, but never as aliases. Select @LocalVar = Column1 FROM Table

Is there any reason to prefer B over A? I can't seem to find much on A searching.

Solution

  • Not many RDBMS support the equals syntax, but it is commonly used by VB/C# application developers who then learn MS SQL Server due to the similarity to assigning a value to a variable, that is certainly one way to look at it, and although I cannot find a reference, this syntax was probably adopted to encourage developers to adopt the MS SQL platform.

    The problem is that this usage is ambiguous because the same syntax can be used to actually assign the value of a column into a variable, or in an UPDATE statement to assign the value into another column:

    -- assign the variable the value from Column1
SELECT @MyVar = Column1 FROM Table

-- Update Column2 with the value from Column1
UPDATE Column2 = Column1 FROM Table

    Using the AS keyword is supported in all SQL RDBMS, it is a lot less ambiguous as similar syntax only exists inside CAST functions.

    AS is more expressive, it is very clear what the intent is and cannot be mistaken to be an assignment.

    Using AS is also considered more standard because the same syntax can be used to alias tables, views and other record sets in the SQL query.

    It is worth noting that AS is technically optional in most RDBMS, so you could leave it out altogether, but doing this can lead to other issues, especially if the alias you wanted to use actually exists as a real name of a column or a table. Reasons to use 'AS' are documented here: https://www.databasestar.com/sql-alias/

    So while these variants will all work to alias a column in SQL Server:

    SELECT Column1 AS [alias] from Table
SELECT Column1 AS 'alias' from Table
SELECT Column1 [alias] FROM Table
SELECT [alias] = Column1 FROM Table

    Only AS (or omitting it) works for aliasing a table

    SELECT Column1 AS [alias] from Table as t
SELECT Column1 [alias] FROM Table t
SELECT Column1 alias FROM Table [t]

    The following will NOT work for table aliases
    Note here that we can't alias a table to a string value, it must be a tokenized name

    SELECT Column1 AS 'alias' from Table AS 't'
SELECT [alias] = Column1 FROM t = Table

    Using AS is the most commonly supported syntax across all tables for any type of alias in a query, not just columns. For this reason you will find it as the most commonly documented syntax as well.