August 3, 2023
Summary: in this tutorial, you will learn about the PostgreSQL table aliases and their practical applications.
Introduction to the PostgreSQL table aliases
Table aliases temporarily assign tables new names during the execution of a query.
The following illustrates the syntax of a table alias:
table_name AS alias_name;
In this syntax, the
table_name is assigned an alias as
alias_name. Similar to column aliases, the
AS keyword is optional. It means that you omit the
AS keyword like this:
Practical applications of table aliases
Table aliases have several practical applications.
1) Using table aliases for the long table name to make queries more readable
If you must qualify a column name with a long table name, you can use a table alias to save some typing and make your query more readable.
For example, instead of using the following expression in a query:
you can assign the table
a_very_long_table_name an alias like this:
a_very_long_table_name AS alias
And reference the
column_name in the table
a_very_long_table_name using the table alias:
2) Using table aliases in join clauses
Typically, you often use a join clause to query data from multiple tables that have the same column name.
If you use the same column name that comes from multiple tables without fully qualifying them, you will get an error.
To avoid this error, you need to qualify these columns using the following syntax:
To make the query shorter, you can use the table aliases for the table names listed on
INNER JOIN clauses. For example:
SELECT c.customer_id, first_name, amount, payment_date FROM customer c INNER JOIN payment p ON p.customer_id = c.customer_id ORDER BY payment_date DESC;
3) Using table aliases in self-join
When you join a table to itself (a.k.a self-join), you need to use table aliases. This is because referencing the same table multiple times within a query results in an error.
The following example shows how to reference the
employee table twice in the same query using the table aliases:
SELECT e.first_name employee, m .first_name manager FROM employee e INNER JOIN employee m ON m.employee_id = e.manager_id ORDER BY manager;
In this tutorial, you have learned how to use PostgreSQL table aliases to temporarily assign new names to tables during the execution of a query.