6 min read

SQL joins: INNER JOIN, LEFT JOIN and finding missing rows

A join combines rows from two tables using a column they share, usually a foreign key. The examples use the sample store from the SQL challenges, where products.category_id points to categories.category_id.

INNER JOIN: only rows that match

An inner join keeps a row only when both tables have a match. This lists products with the name of their category.

SELECT p.name AS product, c.name AS category
FROM products p
JOIN categories c ON c.category_id = p.category_id
ORDER BY c.name, p.name;

Showing 8 of 30 rows.

LEFT JOIN: keep every row from the left table

A left join keeps every row from the first table, even when the second has no match. The missing side is filled with NULL. That makes it the tool for questions like "which products have never been ordered?"

SELECT p.name, oi.order_item_id
FROM products p
LEFT JOIN order_items oi ON oi.product_id = p.product_id
WHERE oi.order_item_id IS NULL;

Products with no order items: the NULL shows there was no match.

The WHERE ... IS NULL filter keeps only the unmatched rows. Putting that condition in the ON clause instead would return every product, which is a common mistake.

Joining several tables

Follow the foreign keys from one table to the next. Revenue per category needs four tables: categories → products → order items → orders.

SELECT c.name, ROUND(SUM(oi.quantity * oi.unit_price), 2) AS revenue
FROM categories c
JOIN products p     ON p.category_id = c.category_id
JOIN order_items oi ON oi.product_id = p.product_id
JOIN orders o       ON o.order_id = oi.order_id
WHERE o.status <> 'cancelled'
GROUP BY c.name
ORDER BY revenue DESC;

Tips

• Give each table a short alias (p, c) and prefix every column with it.
• If your row count is suddenly much larger than expected, a join condition is probably missing or wrong.
• Use an inner join when you only want matches, and a left join when the "nothing matched" case matters.