Run this join:

SELECT id, name, order_date
FROM orders
JOIN customers ON customer_id = customers.id;

ERROR: column reference "id" is ambiguous

Both orders and customers have an id column. PostgreSQL does not guess which one you mean. A column name that is in only one of the tables, such as order_date, works alone. A name that is in both must say its table.

Give each table a short name

A table alias is a short name for a table in one query. Write it after the table name:

SELECT o.id, o.status, c.email
FROM orders AS o
JOIN customers AS c ON o.customer_id = c.id;

• AS is optional for tables: FROM orders o is the same.
• The alias is valid only in this query.
• After you give an alias, use it. orders.id now fails:

ERROR: invalid reference to FROM-clause entry for table "orders"
HINT: Perhaps you meant to reference the table alias "o".

Name the result columns

products and categories both have a name column. Both result columns get the heading name. Column aliases (Chapter 3) give them clear headings:

SELECT p.name AS product, c.name AS category
FROM products p
JOIN categories c ON p.category_id = c.id;