Unit 7 · Data that lasts
SQL JOIN without ON: why rows multiply
Without ON, a join pairs every row of one table with every row of the other: five orders and three customers give fifteen rows. Tell SQL how they match: ON orders.customer_id = customers.id.
The mix-up
What people often think
“Naming both tables is enough for SQL to match them up.”
What the tutor says
Without ON, SQL pairs every order with every customer. Tell it how they match: ON orders.customer_id = customers.id.
Five orders times three customers is how many rows?
How it works
Match the foreign key to the key it points at. order_id is the order’s own number; the link to the customer is customer_id, which holds the customer’s id.
An example
SELECT orders.id, customers.name
FROM orders
JOIN customers ON orders.customer_id = customers.id;Close cousins of this mix-up
The tutor watches for these in the same lesson.
“The two ids should match: order_id with the customer's id.”
order_id is the order's own number. The link is customer_id, which holds the customer's id.
Practise it with a tutor watching
Relationships
Link orders to customers with an id and join the two tables in one query.
Free with an account. The first two units need no account.