sqlstreak

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.

Practise it now