SQL for Analytics II — Joins & Windows

A join pairs each row of one table with the rows of another that match on a key. To price an order we pair its product_id with the products row carrying the same id; to place it in a city we pair its customer_id with the matching customers row. The engine walks the orders and, for each, finds the matching dimension row and glues the columns together into one wider row.

SELECT o.order_id, o.quantity, p.price
FROM orders o
JOIN products p ON o.product_id = p.product_id;

Three orders, each matching exactly one product, gives three joined rows — o1+10.0, o2+20.0, o3+10.0. The ON clause is the matching rule; USING (product_id) is shorthand when the key column has the same name on both sides.