Joins, Aggregation & Subqueries

The pizza shop with two notebooks 🍕

A pizza shop keeps two notebooks. One lists the customers (number, name). The other lists the orders, and each order only says "customer #3 paid 25.00". The boss asks: "How much did each customer spend this month? And who has not ordered at all?"

You flip between the notebooks, matching customer numbers: that is a join. You add up each customer's orders: that is aggregation. You spot the names that never show up in the order notebook: that is the job of an outer join or a subquery.

Last lesson you learned to split data into separate tables so every fact lives once. This lesson is the other half: putting it back together whenever you ask a question.

What you'll learn

• INNER JOIN and LEFT JOIN, and when each one is right
• Why a filter in ON and a filter in WHERE behave differently on a left join
• Summaries with COUNT, SUM, AVG, MIN, MAX, GROUP BY and HAVING
• The trap of COUNT(*) versus COUNT(column)
• Subqueries, EXISTS and CTEs (WITH), and why NOT IN can silently return nothing

In the labs you keep writing SQL for SQLite inside Python strings.

Joins: matching rows across tables

Take these two tables:

-- customers: (1, Amal) (2, Bilal) (3, Carla)
-- orders:    (id 1, customer_id 1, 2500) (id 2, customer_id 1, 900) (id 3, customer_id 2, 4100)

SELECT c.name, o.id, o.total_cents
FROM customers AS c
JOIN orders AS o ON o.customer_id = c.id;

FROM customers AS c JOIN orders AS o ON ... says: pair every customer row with every order row, and keep only the pairs where the ON condition is true. The short names c and o (aliases) save typing and tell columns apart.

The result is one row per matching pair: Amal appears twice (two orders), Bilal once, and Carla does not appear at all because no order matched her. That is an INNER JOIN (plain JOIN means the same thing).

To keep every customer, use a LEFT JOIN: keep every row from the left table, and fill the right-side columns with NULL when nothing matches.

SELECT c.name, o.id, o.total_cents
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.id;
-- Carla now appears once, with o.id = NULL and o.total_cents = NULL

The ON versus WHERE trap

Suppose you want every customer plus only their orders of 1000 or more.

-- WRONG: Carla is kept by the join, then WHERE throws her away (NULL >= 1000 is not true).
... LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.total_cents >= 1000

-- RIGHT: the filter is part of the matching rule, so unmatched customers survive.
... LEFT JOIN orders o ON o.customer_id = c.id AND o.total_cents >= 1000

ON decides which rows match. WHERE runs after the join and removes finished rows. On an inner join the two are equivalent, but on a left join they are not.

Two more things worth knowing

• Forgetting the join condition makes a cross join: every row of one table paired with every row of the other. 3 customers and 4 orders give 12 rows, and 10,000 by 10,000 gives 100 million. If a query is suddenly slow and huge, look for a missing ON.
• RIGHT JOIN is a LEFT JOIN with the tables swapped. Most people simply write a LEFT JOIN with the main table first.

Aggregation: from many rows to one answer

An aggregate function reads many rows and returns one value:

| Function | Meaning | |---|---|

Since tables are not supported here, think of the five you will use daily: COUNT (how many), SUM (total), AVG (mean), MIN and MAX.

SELECT COUNT(*), SUM(total_cents), MAX(total_cents) FROM orders;   -- one row for the whole table

Add GROUP BY to get one row per group instead:

SELECT customer_id, COUNT(*) AS orders, SUM(total_cents) AS spent
FROM orders
GROUP BY customer_id;

Rule of thumb: every column in SELECT is either listed in GROUP BY or wrapped in an aggregate function. Most databases enforce it, because "which of the group's ten values should I show?" has no answer.

WHERE versus HAVING

Think of the order in which the database works:

1. FROM / JOIN: build the rows
2. WHERE: remove rows (before grouping)
3. GROUP BY: form the groups
4. HAVING: remove groups (after aggregating)
5. SELECT, then ORDER BY, then LIMIT

SELECT dept_id, COUNT(*) AS headcount, ROUND(AVG(salary), 1) AS avg_salary
FROM employees
WHERE salary >= 3000            -- employees below 3000 are never counted
GROUP BY dept_id
HAVING COUNT(*) >= 2            -- departments with fewer than 2 remaining employees disappear
ORDER BY avg_salary DESC;

WHERE COUNT(*) >= 2 is an error: when WHERE runs, no group exists yet.

COUNT(*) versus COUNT(column)

COUNT(*) counts rows. COUNT(column) counts rows where that column is not NULL. All the other aggregates (SUM, AVG, MIN, MAX) also ignore NULLs.

This matters on a left join. Carla has no orders, but the join still produced one row for her (with NULLs):

SELECT c.name, COUNT(*) AS wrong, COUNT(o.id) AS right
FROM customers c LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.id;
-- Carla: wrong = 1, right = 0

Also note that SUM of zero rows is NULL, not 0. Wrap it: COALESCE(SUM(x), 0) gives 0 when nothing matched.

Subqueries, EXISTS and CTEs

A subquery is a query inside another query. It can produce a single value, a list, or a whole table.

-- A single value: employees paid more than the company average
SELECT name FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

-- A list: customers who placed at least one order
SELECT name FROM customers
WHERE id IN (SELECT customer_id FROM orders);

A correlated subquery mentions a column of the outer query, so it is re-evaluated for each outer row. This finds employees who earn more than their own department's average:

SELECT e.name
FROM employees e
WHERE e.salary > (SELECT AVG(x.salary) FROM employees x WHERE x.dept_id = e.dept_id);

Readable steps with a CTE

A CTE (common table expression, WITH) gives a subquery a name, so a long query reads top to bottom:

WITH dept_avg AS (
  SELECT dept_id, AVG(salary) AS avg_salary FROM employees GROUP BY dept_id
)
SELECT e.name, e.salary, d.avg_salary
FROM employees e JOIN dept_avg d ON d.dept_id = e.dept_id
WHERE e.salary > d.avg_salary;

"Who has none?" and the NOT IN trap

To find rows with no match, the natural query is NOT IN:

SELECT name FROM products
WHERE id NOT IN (SELECT product_id FROM order_items);   -- dangerous!

If even one product_id in the subquery is NULL, this returns no rows at all. Why? id NOT IN (1, 2, NULL) means id <> 1 AND id <> 2 AND id <> NULL. The last comparison is unknown, never true, so the whole condition can never be true. There is no error, only a silently empty answer.

Two safe forms:

-- 1. NOT EXISTS: does a matching row exist? NULLs never match, so they cause no harm.
SELECT p.name FROM products p
WHERE NOT EXISTS (SELECT 1 FROM order_items i WHERE i.product_id = p.id);

-- 2. LEFT JOIN, then keep the rows where the right side is missing.
SELECT p.name FROM products p
LEFT JOIN order_items i ON i.product_id = p.id
WHERE i.id IS NULL;

EXISTS only asks "is there at least one row?", so what the inner SELECT returns does not matter (by convention SELECT 1). Use NOT EXISTS or the LEFT JOIN form, and keep NOT IN for fixed lists such as NOT IN ('a', 'b').

Recap and what to practice next

• A join matches rows through a condition. INNER keeps matched pairs only; LEFT keeps every left row and fills the right with NULL.
• On a left join, put filters about the right table in ON; a WHERE on it removes the NULL rows again.
• GROUP BY makes one row per group. WHERE filters rows before grouping, HAVING filters groups after.
• COUNT(*) counts rows, COUNT(column) counts non-NULL values, and aggregates ignore NULL. SUM of nothing is NULL: use COALESCE.
• Subqueries and CTEs break a question into steps. Prefer NOT EXISTS over NOT IN when NULLs are possible.

Practice next: take the two tables from the first lab and answer new questions: the customer with the highest total, the average order per customer, customers whose every order is above 1000 (hint: compare MIN). Then read a query plan: the next lesson shows why a join on a big table is fast with the right index and painfully slow without one.