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 COUNT(c1)
, COUNT(*)
FROM (VALUES (1)
, (NULL)
) t1(c1)
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 t1 (c1) AS (
VALUES (1)
, (NULL)
)
SELECT COUNT(c1)
, COUNT(*)
FROM t1
Compatibility
Even though from
aliases were already required for intermediate SQL-92 and became mandatory in SQL:1999, with
is nevertheless better supported:
- Only for derived tables. Since MySQL 8.0.
- Accepts a
<derived column list>
with fewer columns than the base table has. - Not for regular tables or views.
With
is supported since 9iR2, but column aliases only since 11gR2 (ORA-32033).- Only allowed at the very begin of a statement. E.g.
with...insert...select
.