Joins

Tables rarely live alone; joins stitch them. Master these four and you’re set.

1 · INNER JOIN (the default)

SELECT u.id, o.id, o.total
FROM   users AS u
JOIN   orders AS o ON o.user_id = u.id;

Returns rows where the join predicate matches in both tables.

2 · LEFT JOIN (keep left‑hand rows)

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

Users with zero orders still appear; o.* is NULL. Handy for “show latest activity or None”.

3 · RIGHT / FULL OUTER JOIN (rare)

-- right
SELECT * FROM a
RIGHT JOIN b ON b.a_id = a.id;

-- full
SELECT * FROM a
FULL JOIN  b ON b.a_id = a.id;

Use sparingly; often a UNION of two LEFT JOINs is clearer.

4 · WHERE vs ON filtering

-- filter before join
SELECT * FROM a
JOIN b ON b.a_id = a.id AND b.active;

-- filter after join
SELECT * FROM a
JOIN b ON b.a_id = a.id
WHERE b.active;

ON filters rows before they join, reducing work; WHERE filters the joined result. Know the difference or you’ll change row counts.

5 · Good habits

• Qualify every column (table.col)—future schema edits won’t break queries. Official doc
• Index join keys on both sides.
• Check row counts before & after join when debugging.

ASCII refresher

INNER      LEFT         RIGHT         FULL
a ∩ b      a ⊆ a⋃b      b ⊆ a⋃b      a⋃b