How SQL joins work — the mental model developers need

A Join Is a Row Matcher

A join combines columns from two tables by matching rows that satisfy a condition — usually that a foreign key in one table equals a primary key in another.

-- orders has a customer_id column that references customers.id
SELECT
  o.id          AS order_id,
  c.name        AS customer_name,
  o.amount
FROM orders o
JOIN customers c ON o.customer_id = c.id;

Here o and c are table aliases — shorter names used within the query. The ON clause specifies the matching condition.

Without a Join Condition: Cartesian Product

If you join two tables without ON (or with a cross join), every row from the first table is paired with every row from the second. 100 orders × 50 customers = 5000 rows. This is almost never what you want:

-- Dangerous: produces every combination
SELECT * FROM orders, customers;

Join Columns Don't Have to Share a Name

The columns being compared just need compatible types. orders.customer_id joins to customers.id — different names, same values. The column naming is a convention, not a requirement.

-- Multiple join conditions are valid
SELECT * FROM shipments s
JOIN warehouses w
  ON s.warehouse_code = w.code
  AND s.region = w.region;