Joins Meet WHERE and GROUP BY

Joins compose with everything you know

A join's output is just rows, so every tool from units 2 through 4 works on it. Filter joined rows with WHERE:

SELECT customers.name, orders.item
FROM orders
JOIN customers ON customers.id = orders.customer_id
WHERE customers.city = 'Tokyo'
ORDER BY orders.id;

The join runs first in your mental model (build the combined rows), then WHERE keeps only the rows whose customer lives in Tokyo. Note the power move here: you filtered orders using a column that only exists in customers.

Filtering a join on a column from the other table

WHERE can test any column in the joined result, including one that came from the table being joined in. Only Ben lives in Tokyo, so only his two orders survive.

CREATE TABLE customers (
  id INTEGER,
  name TEXT,
  city TEXT
);

CREATE TABLE orders (
  id INTEGER,
  customer_id INTEGER,
  item TEXT,
  price INTEGER
);

INSERT INTO customers VALUES (1, 'Ana', 'Lima');
INSERT INTO customers VALUES (2, 'Ben', 'Tokyo');
INSERT INTO customers VALUES (3, 'Cara', 'Paris');

INSERT INTO orders VALUES (101, 1, 'coffee', 5);
INSERT INTO orders VALUES (102, 2, 'sandwich', 8);
INSERT INTO orders VALUES (103, 1, 'bagel', 4);
INSERT INTO orders VALUES (104, 3, 'coffee', 5);
INSERT INTO orders VALUES (105, 2, 'coffee', 5);

SELECT customers.name, orders.item
FROM orders
JOIN customers ON customers.id = orders.customer_id
WHERE customers.city = 'Tokyo'
ORDER BY orders.id;

Output

Ben|sandwich
Ben|coffee

The city is not stored on the orders table at all, yet the query filters orders by city. That is the real payoff of a join: the two tables behave as one wide table for the rest of the query.

Keep the two clauses straight. ON says how rows pair up, and WHERE says which of the paired rows to keep. Moving this city test into the ON clause of an inner join happens to give the same answer, but with a LEFT JOIN in the next unit it would not, so it pays to keep the roles separate.

Join, then group

GROUP BY works on joined rows too. Total spend per customer, by name this time instead of by a repeated name column (compare lesson 4-2):

SELECT customers.name, SUM(orders.price) AS total
FROM orders
JOIN customers ON customers.id = orders.customer_id
GROUP BY customers.name
ORDER BY customers.name;

Build the joined rows, bucket them by name, sum each bucket. This join + GROUP BY combination is probably the single most common query shape in real applications: revenue per product, posts per user, students per course.

Totals per customer across two tables

GROUP BY works on a joined result exactly as it did on a single table. Ana totals 5 + 4, Ben totals 8 + 5, and Cara has a single order.

CREATE TABLE customers (
  id INTEGER,
  name TEXT,
  city TEXT
);

CREATE TABLE orders (
  id INTEGER,
  customer_id INTEGER,
  item TEXT,
  price INTEGER
);

INSERT INTO customers VALUES (1, 'Ana', 'Lima');
INSERT INTO customers VALUES (2, 'Ben', 'Tokyo');
INSERT INTO customers VALUES (3, 'Cara', 'Paris');

INSERT INTO orders VALUES (101, 1, 'coffee', 5);
INSERT INTO orders VALUES (102, 2, 'sandwich', 8);
INSERT INTO orders VALUES (103, 1, 'bagel', 4);
INSERT INTO orders VALUES (104, 3, 'coffee', 5);
INSERT INTO orders VALUES (105, 2, 'coffee', 5);

SELECT customers.name, SUM(orders.price) AS total
FROM orders
JOIN customers ON customers.id = orders.customer_id
GROUP BY customers.name
ORDER BY customers.name;

Output

Ana|9
Ben|13
Cara|5

The buckets are built from a column in customers while the numbers being added come from orders. Neither table alone could answer the question, since one holds the names and the other holds the prices.

Grouping by customers.name is a slight shortcut worth noticing. Two different customers could share a name, and their orders would then land in one bucket, so production code usually groups by customers.id and displays the name alongside it.

Counting orders per city

Grouping by city rather than by name rolls several customers into one bucket, which is the first genuinely new thing a join makes possible.

CREATE TABLE customers (
  id INTEGER,
  name TEXT,
  city TEXT
);

CREATE TABLE orders (
  id INTEGER,
  customer_id INTEGER,
  item TEXT,
  price INTEGER
);

INSERT INTO customers VALUES (1, 'Ana', 'Lima');
INSERT INTO customers VALUES (2, 'Ben', 'Tokyo');
INSERT INTO customers VALUES (3, 'Cara', 'Paris');

INSERT INTO orders VALUES (101, 1, 'coffee', 5);
INSERT INTO orders VALUES (102, 2, 'sandwich', 8);
INSERT INTO orders VALUES (103, 1, 'bagel', 4);
INSERT INTO orders VALUES (104, 3, 'coffee', 5);
INSERT INTO orders VALUES (105, 2, 'coffee', 5);

SELECT customers.city, COUNT(*) AS orders_made
FROM orders
JOIN customers ON customers.id = orders.customer_id
GROUP BY customers.city
ORDER BY customers.city;

Output

Lima|2
Paris|1
Tokyo|2

Lima collects Ana's two orders, Tokyo collects Ben's two, and Paris collects Cara's one. "How many orders" is COUNT(*) rather than SUM, and customers.city replaces the name in all three of SELECT, GROUP BY, and ORDER BY.

The counts total 5, matching the five order rows. A city with several residents would still produce exactly one output row, which is what makes this a genuine geographic report rather than a per-person list.

Grouping by the wrong column

Selecting customers.city with COUNT(*) while writing GROUP BY customers.name is wrong because the buckets are then per name, so two customers from the same city produce two separate rows instead of one combined city row.

The GROUP BY column defines the buckets, always. Grouping by name makes one bucket per customer, and a second Lima resident would give Lima two output rows, each with its own partial count. The rule is to group by the thing you want exactly one row of.

With this three-customer dataset every city has exactly one resident, so the two versions return identical output. That is precisely what makes the bug dangerous: it passes a casual eyeball check on small data and only misreports once the table grows.

Strict databases such as PostgreSQL reject a query that selects a column which is neither grouped nor aggregated, catching this at parse time. SQLite and older MySQL accept it and pick an arbitrary value, which is worth knowing before you trust the result.