SQL Reference ยท Statements and Clauses
SQL AS (aliases)
Reference for the SQL AS keyword: give columns and tables temporary names, use aliases in ORDER BY and self joins, quoting rules and dialect differences.
AS gives something a temporary name for the duration of one query. A column alias renames a column or expression in the result. A table alias gives a table, view or subquery a shorter name that you use in the rest of the query.
Aliases make results readable (price * quantity AS line_total), shorten long table names, and are required when the same table appears twice, as in a self join.
SELECT p.name AS product, p.price * 2 AS double_price FROM products AS p;
Try it
Press Run (or Ctrl + Enter) to see the result here.
Press Run (or Ctrl + Enter) to see the result here.
Syntax
SELECT expression [AS] column_alias FROM table_name [AS] table_alias;
| Part | Description |
|---|---|
column_alias |
A name for the output column. Quote it with "..." if it contains spaces or is a reserved word. |
table_alias |
A name for a table, view or subquery. After you alias a table, refer to it only by the alias. |
AS |
Optional in most dialects but recommended for readability. |
Notes: an alias exists only inside the query. It does not rename anything in the database.
Dialect differences
| Behavior | SQLite | PostgreSQL | MySQL | SQL Server | Oracle |
|---|---|---|---|---|---|
AS for columns |
Optional | Optional | Optional | Optional | Optional |
AS for tables |
Optional | Optional | Optional | Optional | Not allowed |
| Quote style for spaces | "x", [x], `x` |
"x" |
`x` |
[x] or "x" |
"x" |
| Alias folded to lowercase | Keeps case | Yes, unless quoted | Keeps case | Keeps case | Uppercase, unless quoted |
-- Oracle: table alias without AS SELECT p.name FROM products p;
PostgreSQL also needs AS for a column alias that is a keyword such as end; always writing AS avoids that trap.
Usage notes
- Where an alias is visible. Column aliases can be used in
ORDER BY. InWHERE,GROUP BYandHAVINGthey are not portable (MySQL, SQLite and PostgreSQL allow some uses). Repeat the expression or use a subquery. - Forgetting a comma before an unaliased column can turn it into an alias by accident:
SELECT name price FROM productsreturns one column calledpricecontaining names. WritingASmakes the mistake easier to see. - Derived tables need an alias in PostgreSQL, MySQL and SQL Server.
- Use meaningful aliases:
cforcustomersis fine, butt1,t2in a long query hides what is going on. - Snake case (
line_total) avoids quoting. Spaces and capitals force quotes everywhere you use the name.
Related
SELECT,FROM, self join, derived tables- Lessons: Aliases, SELECT in depth