[go: up one dir, main page]
More Web Proxy on the site http://driver.im/

DE ]

Assign Names to Columns Without Known Name


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:

BigQueryDb2 (LUW)MariaDBMySQLaOracle DBdPostgreSQLbSQL ServerceSQLiteOption 1: from clauseOption 2: with clause
  1. Only for derived tables. Since MySQL 8.0.
  2. Accepts a <derived column list> with fewer columns than the base table has.
  3. Not for regular tables or views.
  4. With is supported since 9iR2, but column aliases only since 11gR2 (ORA-32033).
  5. Only allowed at the very begin of a statement. E.g. with...insert...select.

You can’t catch up on 20 years of SQL evolution in one day. Subscribe the newsletter via E-Mail, Bluesky or RSS to gradually catch up and to keep modern-⁠sql.com on your radar.

About the Author

Photo of Markus Winand

Markus Winand provides insights into SQL and shows how different systems support it at modern-sql.com. Previously he made use-the-index-luke.com, which is still actively maintained. Markus can be hired as trainer, speaker and consultant via winand.at.

Buy the 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 Amazon.com.

Hire Markus

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

Footnotes

  1. ISO/IEC 9075-2:2023 §7.3 SR 4.

Connect with Markus Winand

Subscribe mailinglistsSubscribe the RSS feedMarkus Winand on LinkedInMarkus Winand on XINGMarkus Winand on TwitterMarkus Winand on Bluesky
Copyright 2015-2025 Markus Winand. All righs reserved.
Legal | Contact | NO WARRANTY | Trademarks | Privacy and GDPR