Skip to main content

Joins and aggregation — one answer from many tables

You wrote a query for "sales by customer" and the total came out double. A customer with zero orders showed 1 order. Looking for unused coupons returned nothing.

Each is executed correctly as SQL. Only the returned count differs from intent. The usual causes are three. A join changed the row count, aggregation granularity shifted, or NULL dropped rows. This chapter looks at how each happens. Causes go beyond these — for instance, boundary time zone in date ranges also changes counts (Indexes).

What you'll learn in this chapter​

  • The evaluation order of SELECT and two constraints it implies
  • Capturing inner vs. outer joins by row-count changes
  • One-to-many joins increasing parent rows
  • The granularity GROUP BY decides, and choosing between WHERE and HAVING
  • Why COUNT(*) and COUNT(column) differ
  • NULL three-valued logic and the condition where NOT IN returns zero rows
  • Triaging "the count doesn't add up"
Prerequisites

Any experience writing SQL SELECT and WHERE is enough. The discussion involves the need for indexes on join columns, so reading Indexes first speeds understanding.

What this chapter does not cover​

TopicSee
Table design and normalizationDatabase Design Guide — Normalization
Many-to-many junction table designDatabase Design Guide — Many-to-many tables
Reading EXPLAIN and index design for join columnsDatabase Design Guide — Introduction to index design
N+1 problem in ORM listingsLaravel API Development Guide — Listing APIs
Subqueries, CTEs, and window functionsSubqueries and window functions

Tables used in examples​

All queries below are executed against this schema and data.

CREATE TABLE customers (
  id          INTEGER PRIMARY KEY,
  name        TEXT NOT NULL,
  plan        TEXT,                              -- NULL if unset
  referrer_id INTEGER REFERENCES customers(id)   -- Referrer. NULL if none
);

CREATE TABLE coupons (
  id   INTEGER PRIMARY KEY,
  code TEXT NOT NULL
);

CREATE TABLE orders (
  id          INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL REFERENCES customers(id),
  ordered_on  TEXT    NOT NULL,                  -- ISO 8601 date (lex sort matches date order)
  amount      INTEGER NOT NULL,
  coupon_id   INTEGER REFERENCES coupons(id)     -- NULL if unused
);

customers has 5 rows.

idnameplanreferrer_id
1Satostandard(NULL)
2Suzukipremium1
3Tanaka(NULL)1
4Takahashistandard(NULL)
5Itopremium2

orders has 7 rows.

idcustomer_idordered_onamountcoupon_id
112026-07-0330001
212026-07-155000(NULL)
322026-07-205000(NULL)
432026-08-0112002
552026-08-059500(NULL)
622026-08-102000(NULL)
712026-08-121500(NULL)

coupons has 3 rows (1 SUMMER / 2 NEWBIE / 3 AUTUMN), and AUTUMN is never used.

Remember two points: Takahashi has zero orders, and Sato has three. Later examples run on this skew.

What order does SELECT get evaluated in​

Writing order and evaluation order differ. PostgreSQL defines SELECT's processing in this order (PostgreSQL — SELECT). WITH, DISTINCT, UNION, and row locks (FOR UPDATE and its variants) are omitted.

OrderStageDoes
1FROMRead tables; join
2WHEREDrop rows not matching the condition
3GROUP BY and aggregate functionsGroup rows; compute aggregate values
4HAVINGDrop groups not matching the condition
5SELECTCompute output columns. Column aliases are decided here
6ORDER BYSort
7LIMITTake the specified number from the top

Memorizing this order avoids repeatedly stumbling on two constraints beginners hit.

You can't write an aggregate in WHERE. Aggregate values are computed at step 3; at WHERE's step 2, they don't yet exist.

-- Errors: SUM value doesn't exist at WHERE time
SELECT customer_id, SUM(amount) FROM orders
WHERE SUM(amount) >= 5000
GROUP BY customer_id;

On my SQLite 3.51.0, it stops with misuse of aggregate: SUM(). To filter groups by aggregate value, use step 4's HAVING.

You can't use SELECT's aliases in WHERE. Aliases are decided at step 5. MySQL explains "because at the time WHERE clause is evaluated, the column value may not yet be determined" (MySQL — Problems with Column Aliases). The same alias works in ORDER BY because step 6 is after 5.

But implementations may not enforce this. On my SQLite, SELECT amount * 2 AS doubled FROM orders WHERE doubled > 5000 exits 0 with 4 rows. Implementations that allow standard-forbidden syntax aren't rare, so "works on one database" isn't grounds for portability.

Capture joins by row-count changes​

Memorizing joins as "does the left survive" makes row differences untraceable. Capture as how many rows are produced.

Inner join (INNER JOIN) creates rows for each left row equal to the number of right rows satisfying the join condition (PostgreSQL — Table Expressions). If none, 0 rows — the row disappears.

SELECT c.name, o.ordered_on, o.amount
FROM customers AS c
INNER JOIN orders AS o ON o.customer_id = c.id
ORDER BY o.id;

Result is 7 rows. Five customers but 7 rows, and Takahashi (no orders) doesn't appear. Starting from customers, the row count is decided by orders.

Outer join (LEFT JOIN) performs the inner join, then adds rows for left rows with no match, filling the right side with NULL. So at least one row per left row remains (same source).

SELECT c.name, o.ordered_on, o.amount
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.id
ORDER BY c.id, o.id;

Result is 8 rows, with Takahashi's row having ordered_on and amount as NULL. Takahashi having 0 orders and 1 row coexist. This mismatch creates the COUNT trap below.

Outer joins can produce the same result as inner joins depending on WHERE. Adding "value is X" conditions on right-table columns drops the NULL-filled rows. Adding WHERE o.amount >= 3000 to the above gives 4 rows, matching inner-join with the same condition. To keep left while filtering right, put the condition on ON.

-- Keep Tanaka and Takahashi as NULL; join only orders ≥ 3000
SELECT c.name, o.amount
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.id AND o.amount >= 3000
ORDER BY c.id, o.id;

This gives 6 rows, with Tanaka and Takahashi having NULL amount.

Joining two one-to-many multiplies rows​

Trying to get "total order amount" and "number of people referred" per customer in one query breaks the result.

-- Sato's total becomes double the actual
SELECT c.name, COUNT(o.id) AS orders, SUM(o.amount) AS total
FROM customers AS c
LEFT JOIN orders    AS o ON o.customer_id = c.id
LEFT JOIN customers AS r ON r.referrer_id = c.id
GROUP BY c.id, c.name
ORDER BY c.id;

Sato gets orders = 6, total = 19000. Actual is 3 orders and 9500 yen. 3 order rows and 2 referral rows combine to 6 rows, adding the same amount twice.

Changing to COUNT(DISTINCT o.id) fixes the count to 3, but SUM stays 19000. Duplicates can be removed because the target is the row's ID; sum keeps adding duplicated rows. Judging fixed by count alone leaves the amount error.

Fix by aggregating before joining.

SELECT c.name, COALESCE(o.orders, 0) AS orders, COALESCE(o.total, 0) AS total
FROM customers AS c
LEFT JOIN (
  SELECT customer_id, COUNT(*) AS orders, SUM(amount) AS total
  FROM orders GROUP BY customer_id
) AS o ON o.customer_id = c.id
ORDER BY c.id;

Sato returns to 3 orders / 9500 yen. The SELECT inside FROM is called a derived table. Details in Subqueries and window functions.

Joining with self​

To display the referrer's name, use customers twice. The same table with different aliases becomes different tables.

SELECT c.name AS customer, r.name AS referrer
FROM customers AS c
LEFT JOIN customers AS r ON r.id = c.referrer_id
ORDER BY c.id;

Suzuki and Tanaka show Sato; Ito shows Suzuki. Sato and Takahashi (no referrer) get NULL for referrer. Making this INNER JOIN drops the 5 rows to 3. Self-joins are often "add supplementary info to everyone", so defaulting to outer join reduces accidents.

Note that columns used in join conditions like ON r.id = c.referrer_id become search load as rows grow. Indexes on join columns are covered by the same mechanism in Indexes.

GROUP BY decides aggregation granularity​

GROUP BY groups rows with the same values in specified columns. Output is one row per group. How many rows fold is decided only by the specified columns.

SELECT customer_id, COUNT(*) AS orders, SUM(amount) AS total
FROM orders
GROUP BY customer_id
HAVING SUM(amount) >= 5000
ORDER BY total DESC, customer_id;

Returns 3 rows: customer 1 (9500 / 3), customer 5 (9500 / 1), customer 2 (7000 / 2). Customer 3 at 1200 is dropped by HAVING.

WHERE drops rows, HAVING drops groups. The step 2/4 difference in evaluation order translates directly to role difference. Same "≥ 5000" — WHERE amount >= 5000 collects only orders ≥ 5000 each, then sums; HAVING SUM(amount) >= 5000 sums all orders, then keeps groups ≥ 5000. Aggregate conditions can't move to WHERE. The reverse (conditions on the grouping column) can move from HAVING to WHERE, but then WHERE is more natural since rows can be reduced before aggregating.

Can you write a column not in GROUP BY inside SELECT​

Depends on implementation.

DatabaseBehavior
PostgreSQLOutside aggregate expressions, columns not in GROUP BY can't be referenced. Exception is functional dependency — if the grouping column is the table's primary key, other columns can be referenced (SELECT)
MySQLONLY_FULL_GROUP_BY is enabled by default, rejecting queries with columns not in GROUP BY and not functionally dependent (MySQL — GROUP BY Handling)
SQLiteNo restriction. Which row in the group's value is returned is undefined (SQLite — Quirks)

Indeed, in SQLite, SELECT customer_id, ordered_on, SUM(amount) FROM orders GROUP BY customer_id exits 0, with ordered_on holding some row's date within the group. "Works locally" doesn't guarantee returning meaningful values.

COUNT(*) and COUNT(column) differ​

  • count(*) counts input rows
  • count(expr) counts rows where the expression isn't NULL

Source: PostgreSQL — Aggregate Functions. This difference surfaces with outer joins.

SELECT c.name, COUNT(*) AS cnt_star, COUNT(o.id) AS cnt_orders
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.id
GROUP BY c.id, c.name
ORDER BY c.id;

Takahashi's row has cnt_star = 1 and cnt_orders = 0. Rows filled with NULL by outer join are still rows, and COUNT(*) counts them. To know the order count, count against a right-table column.

NULL means "unknown"​

NULL isn't 0 or empty string — it represents unknown value. Comparing unknowns gives unknowns, so comparison results are a third value, neither true nor false.

Executing SELECT NULL = NULL locally returns not 0 or 1 but NULL. And WHERE only passes rows where the result is true. Both false and unknown are dropped. So WHERE plan <> 'premium' doesn't return customers with NULL plan. In the data above, only Sato and Takahashi return; Tanaka with NULL plan is dropped. To see whether it's NULL, use IS NULL.

NOT IN with NULL returns zero​

Find coupons never used. Answer is AUTUMN.

-- Returns 0 rows
SELECT id, code FROM coupons
WHERE id NOT IN (SELECT coupon_id FROM orders);

orders.coupon_id has NULL rows. PostgreSQL explicitly states: "The result of NOT IN is null, not true, if the left-hand expression is null, or if no equal right-hand value is found and any right-hand row is null" (PostgreSQL — Subquery Expressions). Because it can't decide whether 3 equals NULL, the result is unknown and the row doesn't pass.

No error. An empty result returns, so "no unused coupons" is misread.

-- AUTUMN returns
SELECT id, code FROM coupons
WHERE id NOT IN (SELECT coupon_id FROM orders WHERE coupon_id IS NOT NULL);

Removing NULL first works as expected. Safer is rewriting to NOT EXISTS, covered in Subqueries and window functions.

Triaging "the count doesn't add up"​

When you get an unexpected count, check in order. Top to bottom: joins changing the row count, folding, and NULL dropping rows.

  1. Is a join changing the row count? — Replace SELECT content with COUNT(*) and count rows from just FROM and JOIN. More than the original table's row count means a one-to-many join is in. An inner join also drops rows with no partner, so growth and loss happen at once. Switch to LEFT JOIN and see whether the count moves
  2. Is aggregation granularity off? — Check that columns in GROUP BY match the unit you want. Adding order date to what you meant per-customer prevents per-customer folding
  3. Are rows dropped by NULL? — Check whether WHERE's condition has columns that could be NULL, or whether NOT IN is used

Too many → one-to-many joins; too few → inner joins or NULL — starting from these lands fastest.

If those three don't explain it, doubt the conditions themselves. Boundary time zone in date ranges is typical, covered by Indexes.

Common misconceptions​

"Using LEFT JOIN always keeps the left row." — Writing right-table value conditions in WHERE drops NULL-filled rows, giving the same result as inner join. To keep left, put conditions on ON. Conversely, WHERE o.id IS NULL keeps only NULL-filled rows, letting you find "rows with no partner".

"Both COUNT(*) and COUNT(column) count rows." — COUNT(*) is row count; COUNT(column) is the count of rows where that column isn't NULL. The difference shows with outer joins.

"NOT IN is the negation of IN." — With NULL mixed in, NOT IN doesn't become true, returning zero rows. IN side also returns NULL, not false, if NULL is present.

"Whether you can write a column not in GROUP BY is decided by SQL spec." — Both PostgreSQL and MySQL reject except when functional dependency explains it (MySQL through default-enabled ONLY_FULL_GROUP_BY). SQLite accepts. "Works locally" isn't grounds for portability.

"Aggregation errors show as errors." — None of the failures in this chapter produce errors. Double amount, one extra count, zero rows returned — all end normally.

Exercises​

Q1. How many rows does LEFT JOIN of customers (5 rows) and orders (7 rows) produce? What about INNER JOIN?

Answer and explanation

LEFT JOIN is 8 rows; INNER JOIN is 7 rows.

Inner join produces rows for each combination satisfying the join condition. 7 orders each tie to 1 customer, so 7 rows. Takahashi (no orders) disappears here.

Outer join adds "left rows with no partner", filled with NULL. Only Takahashi qualifies, so 7 + 1 = 8.

Not matching the customer count (5) is the point. For one row per customer, joining alone isn't enough — fold with GROUP BY, or aggregate orders first, then join.

Q2. customers LEFT JOIN orders grouped per customer with COUNT(*) and COUNT(o.id) — what does Takahashi's row show for each? Explain.

Answer and explanation

COUNT(*) is 1; COUNT(o.id) is 0.

Takahashi has no orders, so outer join creates one row filling all right columns with NULL. This row exists in the group, so COUNT(*) (counting input rows) answers 1.

Meanwhile COUNT(o.id) counts rows where o.id isn't NULL. The filled row's o.id is NULL, so 0.

For order count, use the latter. For the same reason, SUM(o.amount) returns NULL (not 0), so wrap as COALESCE(SUM(o.amount), 0) to display 0.

Q3. SELECT id, code FROM coupons WHERE id NOT IN (SELECT coupon_id FROM orders) returns zero rows, no error. Explain cause and fix.

Answer and explanation

Cause: orders.coupon_id contains NULL.

NOT IN checks "not equal to each right value". With NULL in the right, "3 not equal to NULL" can't be decided, and the result doesn't become true. Rows that aren't true don't pass WHERE, so any NULL in the right makes the result always empty.

Two fixes:

  • Remove NULL from the right: WHERE id NOT IN (SELECT coupon_id FROM orders WHERE coupon_id IS NOT NULL)
  • Rewrite as NOT EXISTS: WHERE NOT EXISTS (SELECT 1 FROM orders AS o WHERE o.coupon_id = coupons.id)

The latter sees only "does a matching row exist", unaffected by NULL. Both return AUTUMN.

The danger of this bug is that empty looks like a correct answer. When "none" returns, doubt NULL first.

Summary​

  • Evaluation order: FROM → WHERE → GROUP BY and aggregates → HAVING → SELECT → ORDER BY → LIMIT. Both "no aggregates in WHERE" and "no aliases" derive from this
  • Capture joins by how many rows are produced, not "does the left survive". Inner join is the combinations satisfying the condition; outer adds NULL-filled rows
  • Joining two one-to-many multiplies rows, inflating totals. Aggregate before joining
  • WHERE drops rows; HAVING drops groups. Aggregate conditions can't move to WHERE
  • COUNT(*) counts rows; COUNT(column) counts rows where the column isn't NULL
  • NULL comparisons are neither true nor false, so WHERE drops them. NOT IN with NULL in the right is always empty
  • When counts don't add up, too many → one-to-many joins; too few → inner joins or NULL
Related references