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)
     , COUNT(*)
  FROM t1


Renaming columns in the from clause was already required by intermediate level SQL-923 but has become a mandatory feature with SQL:1999.4 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 alias.5 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:

BigQueryDb2 (LUW)MariaDBMySQLaOracle DBPostgreSQLbSQL ServercdSQLiteaccepts <as> prior to table aliasfrom clause column nameswith clause (non-recursive)
  1. Only for derived tables
  2. Accepts a <derived column list> with fewer columns than are contained in the base table
  3. Not for regular tables or views
  4. Only allowed at the very begin of a statement. E.g.

About the Author

Photo of Markus Winand

Markus Winand is the SQL Renaissance Ambassador. He is on a mission to introduce developers to the evolution of SQL in the 21st century. Markus can be hired as trainer, speaker and consultant via

Buy his Book

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

The essence of SQL tuning in 200 pages

Buy now!
(paperback and/or PDF)

Paperback also available at

Hire Markus

Markus offers SQL training and consulting for developers working at companies of any size.
Learn more »


  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.

Connect with Markus Winand

Markus Winand on LinkedInMarkus Winand on XINGMarkus Winand on Twitter
“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 and GDPR | CC-BY-NC-ND 3.0 license