Inner and Left Joins

Karim from Finance asks: “What was our 2025 revenue by product category?” Revenue lives on order_items, the year lives on orders, and the category name lives on categories, two steps away via products. No single table answers the question. Joins put them together, and getting them right is mostly about knowing which rows each join keeps.

Following the keys

Each join needs a condition that says which rows belong together, almost always a key: order_items.order_id points to orders.order_id, order_items.product_id to products.product_id, and so on. For Cartwheel, revenue counts every order except cancelled ones.

SELECT
  c.name AS category,
  COUNT(DISTINCT o.order_id) AS orders,
  ROUND(SUM(oi.quantity * oi.unit_price * (1 - oi.discount)), 2) AS revenue
FROM orders AS o
JOIN order_items AS oi ON oi.order_id = o.order_id
JOIN products    AS p  ON p.product_id = oi.product_id
JOIN categories  AS c  ON c.category_id = p.category_id
WHERE o.status <> 'cancelled'
  AND o.order_date >= '2025-01-01'
  AND o.order_date <  '2026-01-01'
GROUP BY c.name
ORDER BY revenue DESC;

Desks & Chairs leads with about 77,000. Two rows look odd, though: “Kitchen” and “Outdoor” are top-level categories, and products are supposed to sit in sub-categories. Keep that in mind; it’s a data-quality finding you’ll chase in Section 4.

Two habits make join queries readable and safe. Short aliases (o, oi, p, c) keep lines short. And qualifying every column with its alias removes guesswork, which matters here: order_items.unit_price is the price charged on the day, products.unit_price is today’s list price. An unqualified unit_price in this query is an “ambiguous column name” error, and picking the wrong one silently changes revenue.

COUNT(DISTINCT o.order_id) is deliberate too. After joining to items, each order appears once per item line, so COUNT(*) would count lines. That repetition is the subject of the next lesson.

INNER versus LEFT

JOIN means INNER JOIN: a row survives only if it finds a match on the other side. That’s right for revenue, where an item without an order would be meaningless. It’s wrong when the question is about everything on one side, including things with no matches.

Julia asks for each Operations employee’s ticket count, “including people who don’t handle tickets, so I can see who could help in peak season”:

SELECT
  e.employee_id,
  e.first_name,
  e.title,
  COUNT(t.ticket_id) AS tickets
FROM employees AS e
LEFT JOIN support_tickets AS t
  ON t.agent_id = e.employee_id
WHERE e.department = 'Operations'
GROUP BY e.employee_id
ORDER BY tickets DESC;

Eight rows. The four agents have about 200 tickets each; Mark, Samir, Ben and Adam have 0. A LEFT JOIN keeps every row of the left table, here employees, and where no ticket matches, the ticket columns are NULL. COUNT(t.ticket_id) skips those NULLs, which is why the non-agents show 0 rather than 1.

ON versus WHERE in a LEFT JOIN

Now Julia narrows it: “Same list, but count only urgent tickets.” The natural edit adds AND t.priority = 'urgent' to the WHERE clause, and the four non-agents disappear. Their rows have t.priority NULL, the condition is NULL, and WHERE drops them. A right-table filter in WHERE turns a LEFT JOIN back into an INNER JOIN.

The filter belongs in the ON clause, where it decides which tickets match, not which rows survive:

SELECT
  e.first_name,
  COUNT(t.ticket_id) AS urgent_tickets
FROM employees AS e
LEFT JOIN support_tickets AS t
  ON t.agent_id = e.employee_id
 AND t.priority = 'urgent'
WHERE e.department = 'Operations'
GROUP BY e.employee_id
ORDER BY urgent_tickets DESC;

Eight rows again, with zeros where they belong. The rule: conditions on the left table go in WHERE; conditions on the right table go in ON.

Choosing the join, and the starting table

A reliable way to decide: start FROM the table whose rows you want in the answer, the one that matches the grain you wrote down. Then ask of every other table, “is this information required or optional?” Required information gets an inner join, because a row without it shouldn’t be in the answer. Optional information gets a LEFT JOIN, so missing matches show up as NULLs you can count, label or turn into zeros.

For Julia’s list the answer is about employees, so employees comes first, and tickets are optional. For Karim’s revenue the answer is about sales, so every table in the chain is required.

SQLite also supports RIGHT JOIN and FULL OUTER JOIN (since version 3.39), and PostgreSQL has had them for decades. You’ll rarely need them: a right join is a left join with the tables swapped, and most teams write everything as LEFT JOIN so queries read the same way, top to bottom, from the main table outwards.

Finally, check the row count after each join you add. If employees filtered to Operations has 8 rows, the LEFT JOIN plus GROUP BY must also return 8. A number that grows or shrinks unexpectedly tells you a join is doing something you didn’t intend.

The category revenue had one more trap that didn’t bite, because revenue sat on the most detailed table. When a number lives on the less detailed side of a join, sums inflate. That’s next.