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

• Assigning names to columns without known name

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: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:

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. with...insert...select