Joining tables

Primary and foreign keys, INNER and LEFT JOIN.

Data is split across tables to avoid repeating it. A primary key uniquely identifies a row. A foreign key in another table points to it. Suppose orders has user_id pointing to users.id:

INNER JOIN

SELECT users.name, orders.total
FROM users
INNER JOIN orders ON orders.user_id = users.id;

It returns only users that have orders: Ana (twice) and Chen. Bob and Dara do not appear.

LEFT JOIN

SELECT users.name, orders.total
FROM users
LEFT JOIN orders ON orders.user_id = users.id;

It keeps every user and fills NULL where there is no order, so Bob and Dara appear with a missing total. This is how you find users without orders: add WHERE orders.user_id IS NULL.

NoteAlways write the ON condition. Forgetting it can produce every row paired with every other row (a cross join).