Vol. I · A Postgres Handbook

The Query & the Schema

A reading-room reference for writing PostgreSQL by hand — selects to window functions, with the schema in plain view.

EasyJOINs (INNER, LEFT, RIGHT, FULL)

Get all products with their category names

The query

SQL
SELECT
  p.product_id,
  p.name AS product_name,
  c.name AS category_name,
  p.price
FROM products p
JOIN categories c ON p.cat_id = c.cat_id
ORDER BY c.name, p.name;
Tested against PostgreSQL 16

Note

“JOIN (without keyword) defaults to INNER JOIN in PostgreSQL.

Tables referenced