Aliasing Table And Column Names in the FROM Clause


You might already know how to alias0 column names and tables names. But chances are you don't know how to alias the column names of a table in the from clause.

As usual, the syntax is quite simple: table aliases can be followed by an optional column list in parentheses:

FROM … [AS] alias [(<derived column list>)]

Unfortunately, this syntax is troublesome because it assigns the aliases in the order of the columns in the table. Relying on column positions is generally a bad practice—that's why we want to assign proper names in the first place! However, it goes on: In conforming SQL, there must be an alias for every column in the base table.1 You cannot alias just a few columns. All or nothing. Now think about this: what does this mean when adding new columns to the base table?2

Nevertheless it is important to be able to alias columns based on their position because some columns don't have a name or an unknown name. That is the case for the result of table functions, unnest and values.

Use Case

Conforming Alternatives

Starting with SQL:1999 the with clause can also be used to name columns based on their position (without knowing their current name):

WITH t1 (c1) AS (
     VALUES (1)
          , (NULL)
)
SELECT COUNT(c1)
     , COUNT(*)
  FROM t1

Compatibility

Renaming columns in the from clause was already required by intermediate level SQL-923 but has become a mandatory feature with SQL:19994. Its adoption is poor nevertheless.

It's also noteworthy that entry-level SQL-92 implementations don't need to accept the optional keyword as to introduce a table alias5. Databases conforming to intermediate or full SQL-92 or Core SQL:1999 and later must accept the as keyword. The most portable approach is omitting as nevertheless:

About the Author

Photo of Markus Winand

Markus Winand teaches efficient SQL—inhouse and online. He minimizes the development time using modern SQL and optimizes the runtime with smart indexing—for that he also published the book SQL Performance Explained.

Markus’ Book

Cover of “SQL Performance Explained”: Squirrel running on grass

The essence of SQL tuning in 200 pages.

Buy from Markus
(paperback and/or PDF)

Buy from Amazon
(paperback only)

Hire Markus

…to answer your current SQL questions.

The quick and easy way to benefit from his extensive knowledge and experience. Learn more»

Footnotes

  1. Although “alias” is the commonly used term, “rename” is more appropriate (and also used by the standard). Alias typically implies that an object can be referred to by its alias as well as by its original name. The feature described on this page actually renames the objects: you cannot use the original names to refer to these objects anymore.

    I continue to use “alias” here to avoid confusion about renaming tables or columns using alter (not standard, but supported by some products).

  2. SQL:2016-2: §7.6, Syntax Rule 11bii1B

  3. Statements using this feature will fail. Or, more positively: you need to find all statement that rename the base table's columns and add the new column there as well.

  4. SQL:92, §6.3, Leveling Rule 2c.

  5. E051-09 “Rename columns in the FROM clause” in SQL:1999 and later.

  6. SQL:92, §6.3, Leveling Rule 2b.

“modern SQL” by Markus Winand is licensed under a Creative Commons Attribution-Noncommercial-No Derivative Works 3.0 Unported License.
Legal | Contact | NO WARRANTY | Trademarks | Privacy | CC-BY-NC-ND 3.0 license