Renaming Tables And Columns in the FROM Clause

The from as well as the join clause allow to assign a new names to the respective table and its columns. Assigning table names is often done to use shorter names in the statement. Naming columns is most often used to names to columns that don’t have a use-defined name yet.

The syntax is quite simple: Each data source in a from or join clause can be followed by the optional keyword as and the new name under which this data source will be known from there on. Optionally, the parenthesized list of new column names follows.

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

The new names actually replace the old ones in the semantically following clauses (on/using, where, group by, having, window, select).

SELECT b, a
  FROM (VALUES (1, 'a')
       ) t(a, b)

Use Cases

• Assigning names to columns without known name

Conforming Alternatives

The with clause can also assign new names to columns based on their position.

WITH t (a, b) AS (
  VALUES (1, 'a')
)
SELECT b, a
  FROM t

Compatibility

1. Only for values and select: FROM (VALUES | SELECT …) t(c1, c2)
2. Only for select: FROM (SELECT …) t(c1, c2)
3. Only for values, but then required: FROM (VALUES …) t(c1, c2)
4. Only allowed at the very begin of a statement. E.g. with...insert...select

Extensions

Some systems allow omitting a table name where the standard requires one.

Some systems allow renaming fewer columns as the table has so that only the first few columns are renamed.

Related

Related Features

• E051-05, “Select list items can be renamed”

• E051-08, “Correlation names in the FROM clause”

• E051-09, “Rename columns in the FROM clause”