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.

AdvancedJOINs (INNER, LEFT, RIGHT, FULL)

Find products never ordered (LEFT anti-join)

The query

SQL
SELECT
  p.product_id,
  p.name,
  p.price,
  p.stock
FROM products p
LEFT JOIN order_items oi ON p.product_id = oi.product_id
WHERE oi.item_id IS NULL
ORDER BY p.stock DESC;
Tested against PostgreSQL 16

Note

“Products with no orders — useful for inventory cleanup. Alternative: NOT EXISTS or NOT IN.

Tables referenced