|PostgreSQL Reference Manual - Volume 1 - SQL Language Reference|
by The PostgreSQL Global Development Group
Paperback (6"x9"), 716 pages
RRP £32.00 ($49.95)
Sales of this book support the PostgreSQL project! Get a printed copy>>>
126.96.36.199 Table and Column Aliases
A temporary name can be given to tables and complex table references to be used for references to the derived table in the rest of the query. This is called a table alias.
To create a table alias, write
FROM table_reference AS alias
FROM table_reference alias
AS key word is noise.
alias can be any identifier.
A typical application of table aliases is to assign short identifiers to long table names to keep the join clauses readable. For example:
SELECT * FROM some_very_long_table_name s JOIN another_fairly_long_name a ON s.id = a.num;
The alias becomes the new name of the table reference for the current query--it is no longer possible to refer to the table by the original name. Thus
SELECT * FROM my_table AS m WHERE my_table.a > 5;
is not valid according to the SQL standard. In
PostgreSQL this will draw an error if the
add_missing_from configuration variable is
off (as it is by default). If it is
an implicit table reference will be added to the
FROM clause, so the query is processed as if
it were written as
SELECT * FROM my_table AS m, my_table AS my_table WHERE my_table.a > 5;
That will result in a cross join, which is usually not what you want.
Table aliases are mainly for notational convenience, but it is necessary to use them when joining a table to itself, e.g.,
SELECT * FROM people AS mother JOIN people AS child ON mother.id = child.mother_id;
Additionally, an alias is required if the table reference is a subquery (see section 188.8.131.52 Subqueries).
Parentheses are used to resolve ambiguities. In the following example,
the first statement assigns the alias
b to the second
my_table, but the second statement assigns the
alias to the result of the join:
SELECT * FROM my_table AS a CROSS JOIN my_table AS b ... SELECT * FROM (my_table AS a CROSS JOIN my_table) AS b ...
Another form of table aliasing gives temporary names to the columns of the table, as well as the table itself:
FROM table_reference [AS] alias ( column1 [, column2 [, ...]] )
If fewer column aliases are specified than the actual table has columns, the remaining columns are not renamed. This syntax is especially useful for self-joins or subqueries.
When an alias is applied to the output of a
clause, using any of these forms, the alias hides the original
names within the
JOIN. For example,
SELECT a.* FROM my_table AS a JOIN your_table AS b ON ...
is valid SQL, but
SELECT a.* FROM (my_table AS a JOIN your_table AS b ON ...) AS c
is not valid: the table alias
a is not visible
outside the alias
|ISBN 0954612027||PostgreSQL Reference Manual - Volume 1 - SQL Language Reference||See the print edition|