Normally, we define column names using the create statement and alias them in select if needed. However, to alias a column using select you must first know its the original name. This is not the case when using table functions, unnest or values . The only way to assign names to such columns is on the basis of their position.
Option 1: Using Aliases in the from Clause
Besides table aliases, intermediate level SQL-92 also supports renaming columns in the from clause:
SELECT b, a
FROM (VALUES (1, 'a')
) t(a, b)The column names produced by values are implementation-depended.0 That is not useful for portable SQL. To circumvent that glitch, the example assigns the table alias t1 followed by a list of column aliases in parenthesis (just c1 in that case). The select clause can now refer to c1 in a portable manner.
Option 2: Using Common-Table-Expressions (with)
Starting with SQL:1999 the with clause can also be used to rename columns based on their position—i.e., without knowing their original name:
WITH t (a, b) AS (
VALUES (1, 'a')
)
SELECT b, a
FROM tCompatibility
Even though from aliases were already required for intermediate SQL-92 and became mandatory in SQL:1999, with is nevertheless better supported:
