SQL Joins
A join stitches rows from two or more tables together using a matching condition. Joins are how relational databases recombine the data they split into tables.
The four basic shapes
| Join type | Returns |
|---|---|
INNER JOIN | Rows that match in both tables. |
LEFT JOIN | Every row from the left table + matches from the right (NULL where no match). |
RIGHT JOIN | Every row from the right + matches from the left. |
FULL OUTER JOIN | Every row from both sides, with NULLs where no match. |
Quick example
SQL
SELECT c.name, o.total FROM customers c INNER JOIN orders o ON o.customer_id = c.id WHERE o.total > 100;
Joining three or more tables
SQL
SELECT c.name, p.title, oi.quantity FROM customers c JOIN orders o ON o.customer_id = c.id JOIN order_items oi ON oi.order_id = o.id JOIN products p ON p.id = oi.product_id;
USING and NATURAL JOIN (handle with care)
USING (customer_id)— concise when both columns share the same name.NATURAL JOIN— matches on every same-named column. Convenient, dangerous: a future column rename can silently change behaviour.
Tip: Most "JOIN gave me duplicate rows" bugs come from joining on a non-unique key, or joining onto a many-rows-per-parent table. Look at the join condition first.
Example
Example
SELECT c.name, o.total FROM customers c INNER JOIN orders o ON o.customer_id = c.id;Try it Yourself »
Exercise
Keyword that introduces the join condition.
FROM customers c JOIN orders o
o.customer_id = c.id
Two letters; pairs with JOIN.
Discussion
Loading…