Table Aliases

A table alias gives a table a temporary name within a query. Aliases make queries shorter and easier to read, are required for self-joins, and are essential for disambiguating column names when joining multiple tables that share column names.

Table alias syntax

table_name AS alias_name

The AS keyword is optional — both forms are valid:

FROM film AS f   -- with AS
FROM film f      -- without AS (equivalent)

Once an alias is defined, use it throughout the query to qualify column references: alias.column_name.

Basic table alias example

Assign the alias f to the film table and use it to qualify the column:

SELECT f.title
FROM film AS f
ORDER BY f.title
LIMIT 5;

title
------------------
 Academy Dinosaur
 Ace Goldfinger
 Adaptation Holes
 Affair Prejudice
 African Egg
(5 rows)

Using table aliases in joins

Table aliases are most valuable in JOIN queries to avoid ambiguous column names and to keep the SQL readable when joining tables with long names:

SELECT
  c.customer_id,
  c.first_name,
  p.amount,
  p.payment_date
FROM customer c
INNER JOIN payment p ON p.customer_id = c.customer_id
ORDER BY p.payment_date DESC;

customer_id | first_name  | amount |        payment_date
-------------+-------------+--------+----------------------------
         94 | Norma       |   4.99 | 2007-05-14 13:44:29.996577
        264 | Gwendolyn   |   2.99 | 2007-05-14 13:44:29.996577
        263 | Hilda       |   0.99 | 2007-05-14 13:44:29.996577
...

Using table aliases in self-joins

When a table is joined to itself (a self-join), table aliases are required — PostgreSQL needs distinct names to distinguish the two references to the same table:

SELECT
  f1.title,
  f2.title,
  f1.length
FROM film f1
INNER JOIN film f2
  ON f1.film_id <> f2.film_id
  AND f1.length = f2.length;

title            |            title            | length
-----------------+-----------------------------+--------
 Chamber Italian | Resurrection Silverado      |    117
 Chamber Italian | Magic Mallrats              |    117
 Chamber Italian | Graffiti Love               |    117
...

Without aliases f1 and f2, referencing film.title twice would be ambiguous and cause an error.

Practical tips for table aliases

• Keep aliases short but meaningful: single-letter aliases like c for customer and p for payment are common. In complex queries with many tables, use two-letter abbreviations to keep them distinguishable.
• Aliases are query-scoped: a table alias only lives for the duration of the query. It cannot be used outside the query or in a different statement.
• Use aliases to qualify all columns in joins: prefix every column with its table alias in multi-table queries to eliminate ambiguity and make the query easier to audit.
• Aliases are required for self-joins: you cannot reference the same table twice in a FROM clause without giving at least one reference an alias.

Reference: PostgreSQL documentation — Table Expressions.