SQL JOINs explained
Real databases split data across tables that point at each other. Joins put it back together. This guide uses Rocket Pizza, a small shop with customers, orders and a menu.
INNER JOIN: only matches
JOIN … ON pairs each row with rows from another table that satisfy the ON condition. A plain JOIN (short for INNER JOIN) drops rows that have no partner. Short aliases like o and c keep queries readable.
SELECT o.id AS order_id, c.name, o.status FROM orders o JOIN customers c ON c.id = o.customer_id;
LEFT JOIN: keep everyone
LEFT JOIN keeps every row from the left table and fills the right side with NULL when there's no match. That's how you answer “who has never ordered?”: keep the NULLs.
SELECT c.name FROM customers c LEFT JOIN orders o ON o.customer_id = c.id WHERE o.id IS NULL;
Counting with a LEFT JOIN
To include zeros in a count, LEFT JOIN and count a column from the right table. COUNT(*) would count the NULL row as 1.
SELECT c.name, COUNT(o.id) AS orders FROM customers c LEFT JOIN orders o ON o.customer_id = c.id GROUP BY c.id, c.name ORDER BY orders DESC;
RIGHT and FULL OUTER JOIN
RIGHT JOIN is a LEFT JOIN with the tables swapped. Most people just reorder the tables and use LEFT. FULL OUTER JOIN keeps unmatched rows from both sides. Both work in PostgreSQL and in SQLite 3.39 and later.
Joining three or more tables
Chain joins one at a time. Each ON connects the new table to one already in the query.
SELECT o.id AS order_id, m.name AS item, oi.quantity FROM orders o JOIN order_items oi ON oi.order_id = o.id JOIN menu_items m ON m.id = oi.item_id WHERE o.id = 3;
Common mistakes
Forgetting the ON condition (or joining on the wrong column), which multiplies rows: every order paired with every customer.
Filtering the right table of a LEFT JOIN in WHERE, which silently turns it back into an inner join. Put that condition in ON instead.
Summing after a join that duplicates rows, which inflates totals.
Questions
Is JOIN the same as INNER JOIN?+
Yes. JOIN on its own means INNER JOIN.
What's the difference between LEFT JOIN and LEFT OUTER JOIN?+
None. OUTER is optional.
Reading is step one. Now make it stick.
Practise this in a five-minute lesson with instant feedback. No account needed.