SQL (PostgreSQL, MySQL, SQLite) Module 5 – Joining tables

Joining many tables without double counting

Join three or more tables in a readable order, spot the fan-out that inflates SUM and COUNT, and aggregate each child table before joining to keep totals right.

Intermediate
25 minutes
Examples run with sql.js 1.14.2
By MySmartCoPilot

Free: the whole lesson. With a pass: the model answers to its 2 interview questions.

What you will learn

Join three or more tables in a readable order
Detect fan-out that inflates SUM and COUNT
Aggregate before joining to keep totals correct

Before you start

What you’ll learn · Before you start

What you will learn

Join three or more tables in a readable order
Detect fan-out that inflates SUM and COUNT
Aggregate before joining to keep totals correct

Before you start

On this page

Joining more than two tables repeats one step: each join pairs the rows built so far with the matching rows of the next table. What matters is what that step does to the row count. A join from a row to its parent, such as an order line to its product, keeps the count. A join from a row to its children, such as an order to its lines, repeats the row once for every child. Join one order to its 2 lines and to its 2 payments, and every line meets every payment: 4 rows, in which each payment appears twice, so a sum of the payments counts each of them twice. That multiplication is called fan-out, and it is the usual reason a report shows more revenue than the money that came in. This lesson builds a four-table query one join at a time, reproduces the inflated totals, shows why DISTINCT does not repair them, and fixes them by aggregating each child table before the join.

The examples use the mart shop of the practice databases lesson with a payments table added. Press Run on any of them to execute it with sql.js 1.14.2, whose engine is SQLite 3.49.1; every query in this lesson returns the same rows in PostgreSQL 18 and MySQL 8.4.

One row per what?

Before you write a join, say what one row of the result stands for: its grain. orders has one row per order, order_items one row per product in an order, and payments one row per payment. Each join either keeps the grain or makes it finer:

Many-to-one, a lookup. From order lines to products, or from orders to customers, each row finds at most one partner through a foreign key, so the row count stays the same. With an inner join it can only drop, when a partner is missing.
One-to-many. From orders to their lines or to their payments, each row is repeated once for every child. The result takes the child’s grain, which is right when the answer is about the children, such as invoice lines, and wrong when it is about the parent.
Two child tables of the same parent. Joining orders to both their lines and their payments pairs every line of an order with every payment of the same order, so each order becomes lines × payments rows. No grain describes that result, and every sum over it is suspect.

Two child tables of one order multiply into 4 rows

Text description of the diagram

Order 101 has two lines, rice for 29998 paise and dal for 8950, and two payments, card for 19474 and wallet for 19474.

Joining the order to both tables pairs every line with every payment, so the order becomes 2 × 2 = 4 rows: rice with card, rice with wallet, dal with card and dal with wallet.

Summing over those 4 rows counts each line twice and each payment twice: the lines add up to 77896 and the payments to 77896, where the order is really worth 38948 and was paid 38948.

Below them, a dashed arrow leads to the fix: it reduces each child table to one row per order first, one row of line totals and one row of payment totals, so the join gives 1 × 1 = 1 row with 38948 and 38948.

A four-table query, one join at a time

The row count after each join, then the invoice lines SQL · step_by_step.sql

-- One join at a time: the row count after each step, then the invoice lines.
CREATE TABLE customers (customer_id INTEGER PRIMARY KEY, name TEXT NOT NULL);
CREATE TABLE products  (product_id INTEGER PRIMARY KEY, name TEXT NOT NULL, price_paise INTEGER NOT NULL);
CREATE TABLE orders    (order_id INTEGER PRIMARY KEY, customer_id INTEGER NOT NULL REFERENCES customers (customer_id));
CREATE TABLE order_items (
  order_id   INTEGER NOT NULL REFERENCES orders (order_id),
  product_id INTEGER NOT NULL REFERENCES products (product_id),
  quantity   INTEGER NOT NULL,
  PRIMARY KEY (order_id, product_id)
);
CREATE TABLE payments (
  payment_id   INTEGER PRIMARY KEY,
  order_id     INTEGER NOT NULL REFERENCES orders (order_id),
  method       TEXT    NOT NULL,
  amount_paise INTEGER NOT NULL
);
INSERT INTO customers VALUES (1, 'Asha Kulkarni'), (2, 'Vikram Nair'), (3, 'Meera Rathore'), (4, 'Tenzin Dorjee');
INSERT INTO products VALUES
  (1, 'Basmati rice 1 kg', 14999), (2, 'Toor dal 500 g', 8950), (3, 'Masala chai 250 g', 12000),
  (4, 'Coconut oil 1 L', 21500), (5, 'Ragi flour 1 kg', 6800);
INSERT INTO orders VALUES (101, 1), (102, 2), (103, 1), (104, 3);
INSERT INTO order_items VALUES (101, 1, 2), (101, 2, 1), (102, 4, 1), (103, 3, 2), (103, 5, 1), (104, 2, 3);
-- Order 101 was paid in two equal parts; 103 and 104 have no payment yet.
INSERT INTO payments VALUES (1, 101, 'card', 19474), (2, 101, 'wallet', 19474), (3, 102, 'card', 21500);

SELECT count(*) AS orders_only FROM orders;

SELECT count(*) AS plus_customers
FROM orders AS o
JOIN customers AS c ON c.customer_id = o.customer_id;

SELECT count(*) AS plus_items
FROM orders AS o
JOIN customers   AS c  ON c.customer_id = o.customer_id
JOIN order_items AS oi ON oi.order_id   = o.order_id;

SELECT count(*) AS plus_products
FROM orders AS o
JOIN customers   AS c  ON c.customer_id = o.customer_id
JOIN order_items AS oi ON oi.order_id   = o.order_id
JOIN products    AS p  ON p.product_id  = oi.product_id;

SELECT o.order_id,
       c.name                       AS customer,
       p.name                       AS product,
       oi.quantity,
       oi.quantity * p.price_paise  AS line_paise
FROM orders AS o
JOIN customers   AS c  ON c.customer_id = o.customer_id
JOIN order_items AS oi ON oi.order_id   = o.order_id
JOIN products    AS p  ON p.product_id  = oi.product_id
ORDER BY o.order_id, p.product_id;

Output

┌─────────────┐
│ orders_only │
├─────────────┤
│ 4           │
└─────────────┘
┌────────────────┐
│ plus_customers │
├────────────────┤
│ 4              │
└────────────────┘
┌────────────┐
│ plus_items │
├────────────┤
│ 6          │
└────────────┘
┌───────────────┐
│ plus_products │
├───────────────┤
│ 6             │
└───────────────┘
┌──────────┬───────────────┬───────────────────┬──────────┬────────────┐
│ order_id │   customer    │      product      │ quantity │ line_paise │
├──────────┼───────────────┼───────────────────┼──────────┼────────────┤
│ 101      │ Asha Kulkarni │ Basmati rice 1 kg │ 2        │ 29998      │
│ 101      │ Asha Kulkarni │ Toor dal 500 g    │ 1        │ 8950       │
│ 102      │ Vikram Nair   │ Coconut oil 1 L   │ 1        │ 21500      │
│ 103      │ Asha Kulkarni │ Masala chai 250 g │ 2        │ 24000      │
│ 103      │ Asha Kulkarni │ Ragi flour 1 kg   │ 1        │ 6800       │
│ 104      │ Meera Rathore │ Toor dal 500 g    │ 3        │ 26850      │
└──────────┴───────────────┴───────────────────┴──────────┴────────────┘

Recorded with sql.js 1.14.2 (SQLite 3.49.1) on macOS 26 arm64. To run it yourself: sqlite3 -box :memory: < step_by_step.sql

The counts tell the story. orders has 4 rows. Adding customers keeps 4, because every order has exactly one customer. Adding order_items makes 6, because the grain is now one row per order line. Adding products keeps 6, because each line has exactly one product. The last query lists the invoice lines, one row per line, with the order, the customer and the product beside it.

Three habits keep long joins readable and correct:

Start from the table the answer is about, here the order lines or the orders that own them, and add the lookups after it.
Put each table on its own line with its ON right below it, and use short aliases that name their table, such as oi for order_items and p for products, so a condition on the wrong alias stands out.
Count as you build. Run count(*) after each new join, and stop the moment it changes when you did not expect it to.

SQL to ER Diagram Paste your CREATE TABLE statements to draw the tables and their foreign keys, and see which joins are lookups and which go to child tables.

Fan-out: two child tables of the same orders

Items and payments joined together, then summed SQL · fan_out.sql

-- Items and payments joined to the same orders, then summed: the totals come out too high.
CREATE TABLE customers (customer_id INTEGER PRIMARY KEY, name TEXT NOT NULL);
CREATE TABLE products  (product_id INTEGER PRIMARY KEY, name TEXT NOT NULL, price_paise INTEGER NOT NULL);
CREATE TABLE orders    (order_id INTEGER PRIMARY KEY, customer_id INTEGER NOT NULL REFERENCES customers (customer_id));
CREATE TABLE order_items (
  order_id   INTEGER NOT NULL REFERENCES orders (order_id),
  product_id INTEGER NOT NULL REFERENCES products (product_id),
  quantity   INTEGER NOT NULL,
  PRIMARY KEY (order_id, product_id)
);
CREATE TABLE payments (
  payment_id   INTEGER PRIMARY KEY,
  order_id     INTEGER NOT NULL REFERENCES orders (order_id),
  method       TEXT    NOT NULL,
  amount_paise INTEGER NOT NULL
);
INSERT INTO customers VALUES (1, 'Asha Kulkarni'), (2, 'Vikram Nair'), (3, 'Meera Rathore'), (4, 'Tenzin Dorjee');
INSERT INTO products VALUES
  (1, 'Basmati rice 1 kg', 14999), (2, 'Toor dal 500 g', 8950), (3, 'Masala chai 250 g', 12000),
  (4, 'Coconut oil 1 L', 21500), (5, 'Ragi flour 1 kg', 6800);
INSERT INTO orders VALUES (101, 1), (102, 2), (103, 1), (104, 3);
INSERT INTO order_items VALUES (101, 1, 2), (101, 2, 1), (102, 4, 1), (103, 3, 2), (103, 5, 1), (104, 2, 3);
-- Order 101 was paid in two equal parts; 103 and 104 have no payment yet.
INSERT INTO payments VALUES (1, 101, 'card', 19474), (2, 101, 'wallet', 19474), (3, 102, 'card', 21500);

SELECT c.name,
       count(*)                           AS joined_rows,
       sum(oi.quantity * p.price_paise)   AS ordered_paise,
       sum(pay.amount_paise)              AS paid_paise
FROM customers AS c
JOIN orders      AS o   ON o.customer_id = c.customer_id
JOIN order_items AS oi  ON oi.order_id   = o.order_id
JOIN products    AS p   ON p.product_id  = oi.product_id
LEFT JOIN payments AS pay ON pay.order_id = o.order_id
GROUP BY c.customer_id, c.name
ORDER BY c.customer_id;

-- What the tables really hold.
SELECT sum(oi.quantity * p.price_paise) AS all_items_paise
FROM order_items AS oi
JOIN products AS p ON p.product_id = oi.product_id;

SELECT sum(amount_paise) AS all_payments_paise FROM payments;

Output

┌───────────────┬─────────────┬───────────────┬────────────┐
│     name      │ joined_rows │ ordered_paise │ paid_paise │
├───────────────┼─────────────┼───────────────┼────────────┤
│ Asha Kulkarni │ 6           │ 108696        │ 77896      │
│ Vikram Nair   │ 1           │ 21500         │ 21500      │
│ Meera Rathore │ 1           │ 26850         │            │
└───────────────┴─────────────┴───────────────┴────────────┘
┌─────────────────┐
│ all_items_paise │
├─────────────────┤
│ 118098          │
└─────────────────┘
┌────────────────────┐
│ all_payments_paise │
├────────────────────┤
│ 60448              │
└────────────────────┘

Recorded with sql.js 1.14.2 (SQLite 3.49.1) on macOS 26 arm64. To run it yourself: sqlite3 -box :memory: < fan_out.sql

This query wants two numbers per customer, what they ordered and what they paid, and joins both child tables of orders to get them. Asha’s order 101 has 2 lines and 2 payments, so it becomes 4 rows; her order 103 has 2 lines and no payment, so the LEFT JOIN keeps its 2 lines with NULL payments. That is 6 joined rows for Asha. Each line of order 101 is counted twice and so is each of its payments, so the report says Asha ordered 108,696 paise and paid 77,896, where the true totals are 69,748 and 38,948. Vikram’s order 102 has 1 line and 1 payment and comes out right, which is why fan-out survives tests on small data: each payment is counted once per line of its order, and each line once per payment, so only orders with several rows in the other child table come out wrong.

The two totals at the end are the check. All the payments together hold 60,448 paise, but the paid column of the report adds up to 99,396. A total after the joins must never be larger than the same total read from its own table.

DISTINCT is not the fix

DISTINCT inside the aggregates SQL · distinct_is_not_a_fix.sql

-- DISTINCT inside the aggregates: the order count comes out right, the paid amount does not.
CREATE TABLE customers (customer_id INTEGER PRIMARY KEY, name TEXT NOT NULL);
CREATE TABLE products  (product_id INTEGER PRIMARY KEY, name TEXT NOT NULL, price_paise INTEGER NOT NULL);
CREATE TABLE orders    (order_id INTEGER PRIMARY KEY, customer_id INTEGER NOT NULL REFERENCES customers (customer_id));
CREATE TABLE order_items (
  order_id   INTEGER NOT NULL REFERENCES orders (order_id),
  product_id INTEGER NOT NULL REFERENCES products (product_id),
  quantity   INTEGER NOT NULL,
  PRIMARY KEY (order_id, product_id)
);
CREATE TABLE payments (
  payment_id   INTEGER PRIMARY KEY,
  order_id     INTEGER NOT NULL REFERENCES orders (order_id),
  method       TEXT    NOT NULL,
  amount_paise INTEGER NOT NULL
);
INSERT INTO customers VALUES (1, 'Asha Kulkarni'), (2, 'Vikram Nair'), (3, 'Meera Rathore'), (4, 'Tenzin Dorjee');
INSERT INTO products VALUES
  (1, 'Basmati rice 1 kg', 14999), (2, 'Toor dal 500 g', 8950), (3, 'Masala chai 250 g', 12000),
  (4, 'Coconut oil 1 L', 21500), (5, 'Ragi flour 1 kg', 6800);
INSERT INTO orders VALUES (101, 1), (102, 2), (103, 1), (104, 3);
INSERT INTO order_items VALUES (101, 1, 2), (101, 2, 1), (102, 4, 1), (103, 3, 2), (103, 5, 1), (104, 2, 3);
-- Order 101 was paid in two equal parts; 103 and 104 have no payment yet.
INSERT INTO payments VALUES (1, 101, 'card', 19474), (2, 101, 'wallet', 19474), (3, 102, 'card', 21500);

SELECT c.name,
       count(*)                       AS joined_rows,
       count(DISTINCT o.order_id)     AS orders,
       sum(DISTINCT pay.amount_paise) AS paid_paise
FROM customers AS c
JOIN orders      AS o   ON o.customer_id = c.customer_id
JOIN order_items AS oi  ON oi.order_id   = o.order_id
LEFT JOIN payments AS pay ON pay.order_id = o.order_id
GROUP BY c.customer_id, c.name
ORDER BY c.customer_id;

Output

┌───────────────┬─────────────┬────────┬────────────┐
│     name      │ joined_rows │ orders │ paid_paise │
├───────────────┼─────────────┼────────┼────────────┤
│ Asha Kulkarni │ 6           │ 2      │ 19474      │
│ Vikram Nair   │ 1           │ 1      │ 21500      │
│ Meera Rathore │ 1           │ 1      │            │
└───────────────┴─────────────┴────────┴────────────┘

Recorded with sql.js 1.14.2 (SQLite 3.49.1) on macOS 26 arm64. To run it yourself: sqlite3 -box :memory: < distinct_is_not_a_fix.sql

count(DISTINCT o.order_id) counts each order once however many times its rows repeat, so the order count is right: 2 for Asha. It is tempting to do the same with the money, but sum(DISTINCT pay.amount_paise) adds up the different amounts, not the different payments. Asha paid order 101 in two equal parts of 19,474 paise, so the distinct sum is 19,474, half of what she paid. The same query is wrong on any data where two lines or two payments in one total happen to cost the same, which real data does all the time. DISTINCT hides the symptom on the test data and keeps the bug.

Aggregate before you join

Each child table reduced to one row per order, then joined SQL · aggregate_first.sql

-- Aggregate each child table to one row per order first, then join: nothing is counted twice.
CREATE TABLE customers (customer_id INTEGER PRIMARY KEY, name TEXT NOT NULL);
CREATE TABLE products  (product_id INTEGER PRIMARY KEY, name TEXT NOT NULL, price_paise INTEGER NOT NULL);
CREATE TABLE orders    (order_id INTEGER PRIMARY KEY, customer_id INTEGER NOT NULL REFERENCES customers (customer_id));
CREATE TABLE order_items (
  order_id   INTEGER NOT NULL REFERENCES orders (order_id),
  product_id INTEGER NOT NULL REFERENCES products (product_id),
  quantity   INTEGER NOT NULL,
  PRIMARY KEY (order_id, product_id)
);
CREATE TABLE payments (
  payment_id   INTEGER PRIMARY KEY,
  order_id     INTEGER NOT NULL REFERENCES orders (order_id),
  method       TEXT    NOT NULL,
  amount_paise INTEGER NOT NULL
);
INSERT INTO customers VALUES (1, 'Asha Kulkarni'), (2, 'Vikram Nair'), (3, 'Meera Rathore'), (4, 'Tenzin Dorjee');
INSERT INTO products VALUES
  (1, 'Basmati rice 1 kg', 14999), (2, 'Toor dal 500 g', 8950), (3, 'Masala chai 250 g', 12000),
  (4, 'Coconut oil 1 L', 21500), (5, 'Ragi flour 1 kg', 6800);
INSERT INTO orders VALUES (101, 1), (102, 2), (103, 1), (104, 3);
INSERT INTO order_items VALUES (101, 1, 2), (101, 2, 1), (102, 4, 1), (103, 3, 2), (103, 5, 1), (104, 2, 3);
-- Order 101 was paid in two equal parts; 103 and 104 have no payment yet.
INSERT INTO payments VALUES (1, 101, 'card', 19474), (2, 101, 'wallet', 19474), (3, 102, 'card', 21500);

SELECT c.name,
       count(o.order_id)                AS orders,
       coalesce(sum(i.ordered_paise), 0) AS ordered_paise,
       coalesce(sum(pay.paid_paise), 0)  AS paid_paise
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.customer_id
LEFT JOIN (SELECT oi.order_id,
                  sum(oi.quantity * p.price_paise) AS ordered_paise
           FROM order_items AS oi
           JOIN products AS p ON p.product_id = oi.product_id
           GROUP BY oi.order_id) AS i
       ON i.order_id = o.order_id
LEFT JOIN (SELECT order_id,
                  sum(amount_paise) AS paid_paise
           FROM payments
           GROUP BY order_id) AS pay
       ON pay.order_id = o.order_id
GROUP BY c.customer_id, c.name
ORDER BY c.customer_id;

Output

┌───────────────┬────────┬───────────────┬────────────┐
│     name      │ orders │ ordered_paise │ paid_paise │
├───────────────┼────────┼───────────────┼────────────┤
│ Asha Kulkarni │ 2      │ 69748         │ 38948      │
│ Vikram Nair   │ 1      │ 21500         │ 21500      │
│ Meera Rathore │ 1      │ 26850         │ 0          │
│ Tenzin Dorjee │ 0      │ 0             │ 0          │
└───────────────┴────────┴───────────────┴────────────┘

Recorded with sql.js 1.14.2 (SQLite 3.49.1) on macOS 26 arm64. To run it yourself: sqlite3 -box :memory: < aggregate_first.sql

The fix is to reduce every child table to one row per order before it meets the others. A query in parentheses in FROM, called a derived table, can be joined like a table once it has an alias. Here one derived table sums the lines of each order and another sums the payments of each order. Each has at most one row per order, so joining both to orders multiplies nothing: 1 × 1 = 1 row per order. The outer query then adds up the orders of each customer. Starting from customers with LEFT JOINs keeps Tenzin, who never ordered, and coalesce turns his empty sums into 0. Asha now shows 69,748 ordered and 38,948 paid, and the paid column adds up to the 60,448 paise in payments.

The same fix has other spellings. The two derived tables can be named first with WITH, as the module on common table expressions shows, or each total can come from its own subquery in the SELECT list, as the module on subqueries shows. They all follow one rule: reduce each child table to the grain of the parent before you join it.

Catch fan-out while you build

Three checks for fan-out SQL · fan_out_checks.sql

-- Three checks that catch fan-out while a query is being built.
CREATE TABLE customers (customer_id INTEGER PRIMARY KEY, name TEXT NOT NULL);
CREATE TABLE products  (product_id INTEGER PRIMARY KEY, name TEXT NOT NULL, price_paise INTEGER NOT NULL);
CREATE TABLE orders    (order_id INTEGER PRIMARY KEY, customer_id INTEGER NOT NULL REFERENCES customers (customer_id));
CREATE TABLE order_items (
  order_id   INTEGER NOT NULL REFERENCES orders (order_id),
  product_id INTEGER NOT NULL REFERENCES products (product_id),
  quantity   INTEGER NOT NULL,
  PRIMARY KEY (order_id, product_id)
);
CREATE TABLE payments (
  payment_id   INTEGER PRIMARY KEY,
  order_id     INTEGER NOT NULL REFERENCES orders (order_id),
  method       TEXT    NOT NULL,
  amount_paise INTEGER NOT NULL
);
INSERT INTO customers VALUES (1, 'Asha Kulkarni'), (2, 'Vikram Nair'), (3, 'Meera Rathore'), (4, 'Tenzin Dorjee');
INSERT INTO products VALUES
  (1, 'Basmati rice 1 kg', 14999), (2, 'Toor dal 500 g', 8950), (3, 'Masala chai 250 g', 12000),
  (4, 'Coconut oil 1 L', 21500), (5, 'Ragi flour 1 kg', 6800);
INSERT INTO orders VALUES (101, 1), (102, 2), (103, 1), (104, 3);
INSERT INTO order_items VALUES (101, 1, 2), (101, 2, 1), (102, 4, 1), (103, 3, 2), (103, 5, 1), (104, 2, 3);
-- Order 101 was paid in two equal parts; 103 and 104 have no payment yet.
INSERT INTO payments VALUES (1, 101, 'card', 19474), (2, 101, 'wallet', 19474), (3, 102, 'card', 21500);

-- 1. One row per order is expected: compare the rows with the distinct orders.
SELECT count(*) AS joined_rows, count(DISTINCT o.order_id) AS distinct_orders
FROM orders AS o
JOIN order_items AS oi  ON oi.order_id  = o.order_id
LEFT JOIN payments AS pay ON pay.order_id = o.order_id;

-- 2. Which orders repeat, and how often.
SELECT o.order_id, count(*) AS copies
FROM orders AS o
JOIN order_items AS oi  ON oi.order_id  = o.order_id
LEFT JOIN payments AS pay ON pay.order_id = o.order_id
GROUP BY o.order_id
HAVING count(*) > 1
ORDER BY o.order_id;

-- 3. A total after the joins against the same total in its own table.
SELECT sum(pay.amount_paise) AS paid_after_joins
FROM orders AS o
JOIN order_items AS oi  ON oi.order_id  = o.order_id
LEFT JOIN payments AS pay ON pay.order_id = o.order_id;

SELECT sum(amount_paise) AS paid_in_payments FROM payments;

Output

┌─────────────┬─────────────────┐
│ joined_rows │ distinct_orders │
├─────────────┼─────────────────┤
│ 8           │ 4               │
└─────────────┴─────────────────┘
┌──────────┬────────┐
│ order_id │ copies │
├──────────┼────────┤
│ 101      │ 4      │
│ 103      │ 2      │
└──────────┴────────┘
┌──────────────────┐
│ paid_after_joins │
├──────────────────┤
│ 99396            │
└──────────────────┘
┌──────────────────┐
│ paid_in_payments │
├──────────────────┤
│ 60448            │
└──────────────────┘

Recorded with sql.js 1.14.2 (SQLite 3.49.1) on macOS 26 arm64. To run it yourself: sqlite3 -box :memory: < fan_out_checks.sql

Three checks catch fan-out before a report ships:

Rows against keys. When the result should have one row per order, count(*) must equal count(DISTINCT o.order_id). Here 8 rows hold only 4 orders.
Who repeats. GROUP BY the key with HAVING count(*) > 1 names the culprits: order 101 has 4 copies and order 103 has 2.
Totals against their source. A sum after the joins must equal the same sum over its own table: 99,396 paise after the joins against 60,448 in payments.

Keep the third check as a test next to the report: it works on any query, however the query was written.

A LEFT JOIN undone by a later JOIN

An inner join after a LEFT JOIN SQL · left_then_inner.sql

-- Customers and the products they ordered. Tenzin has no orders.
CREATE TABLE customers (customer_id INTEGER PRIMARY KEY, name TEXT NOT NULL);
CREATE TABLE products  (product_id INTEGER PRIMARY KEY, name TEXT NOT NULL, price_paise INTEGER NOT NULL);
CREATE TABLE orders    (order_id INTEGER PRIMARY KEY, customer_id INTEGER NOT NULL REFERENCES customers (customer_id));
CREATE TABLE order_items (
  order_id   INTEGER NOT NULL REFERENCES orders (order_id),
  product_id INTEGER NOT NULL REFERENCES products (product_id),
  quantity   INTEGER NOT NULL,
  PRIMARY KEY (order_id, product_id)
);
CREATE TABLE payments (
  payment_id   INTEGER PRIMARY KEY,
  order_id     INTEGER NOT NULL REFERENCES orders (order_id),
  method       TEXT    NOT NULL,
  amount_paise INTEGER NOT NULL
);
INSERT INTO customers VALUES (1, 'Asha Kulkarni'), (2, 'Vikram Nair'), (3, 'Meera Rathore'), (4, 'Tenzin Dorjee');
INSERT INTO products VALUES
  (1, 'Basmati rice 1 kg', 14999), (2, 'Toor dal 500 g', 8950), (3, 'Masala chai 250 g', 12000),
  (4, 'Coconut oil 1 L', 21500), (5, 'Ragi flour 1 kg', 6800);
INSERT INTO orders VALUES (101, 1), (102, 2), (103, 1), (104, 3);
INSERT INTO order_items VALUES (101, 1, 2), (101, 2, 1), (102, 4, 1), (103, 3, 2), (103, 5, 1), (104, 2, 3);
-- Order 101 was paid in two equal parts; 103 and 104 have no payment yet.
INSERT INTO payments VALUES (1, 101, 'card', 19474), (2, 101, 'wallet', 19474), (3, 102, 'card', 21500);

-- An inner join after a LEFT JOIN needs a match for the left join's NULLs, so Tenzin disappears.
SELECT c.name, p.name AS product
FROM customers AS c
LEFT JOIN orders      AS o  ON o.customer_id = c.customer_id
JOIN order_items      AS oi ON oi.order_id   = o.order_id
JOIN products         AS p  ON p.product_id  = oi.product_id
ORDER BY c.customer_id, p.product_id;

-- Keep every join after the LEFT JOIN a LEFT JOIN too, and Tenzin stays, with NULLs.
SELECT c.name, p.name AS product
FROM customers AS c
LEFT JOIN orders      AS o  ON o.customer_id = c.customer_id
LEFT JOIN order_items AS oi ON oi.order_id   = o.order_id
LEFT JOIN products    AS p  ON p.product_id  = oi.product_id
ORDER BY c.customer_id, p.product_id;

Output

┌───────────────┬───────────────────┐
│     name      │      product      │
├───────────────┼───────────────────┤
│ Asha Kulkarni │ Basmati rice 1 kg │
│ Asha Kulkarni │ Toor dal 500 g    │
│ Asha Kulkarni │ Masala chai 250 g │
│ Asha Kulkarni │ Ragi flour 1 kg   │
│ Vikram Nair   │ Coconut oil 1 L   │
│ Meera Rathore │ Toor dal 500 g    │
└───────────────┴───────────────────┘
┌───────────────┬───────────────────┐
│     name      │      product      │
├───────────────┼───────────────────┤
│ Asha Kulkarni │ Basmati rice 1 kg │
│ Asha Kulkarni │ Toor dal 500 g    │
│ Asha Kulkarni │ Masala chai 250 g │
│ Asha Kulkarni │ Ragi flour 1 kg   │
│ Vikram Nair   │ Coconut oil 1 L   │
│ Meera Rathore │ Toor dal 500 g    │
│ Tenzin Dorjee │                   │
└───────────────┴───────────────────┘

Recorded with sql.js 1.14.2 (SQLite 3.49.1) on macOS 26 arm64. To run it yourself: sqlite3 -box :memory: < left_then_inner.sql

Long chains of joins have a second trap. customers LEFT JOIN orders keeps Tenzin with NULL in the order columns, but the next join is an inner join to order_items on oi.order_id = o.order_id, which needs an item for that NULL order, finds none and drops the row. A single inner join anywhere after a LEFT JOIN can quietly turn the chain back into inner joins. Keep every join after the LEFT JOIN a LEFT JOIN too, as the second query does. When an order without items should not count, join the inner part first, in parentheses: LEFT JOIN (orders AS o JOIN order_items AS oi ON oi.order_id = o.order_id) ON o.customer_id = c.customer_id keeps every customer and still requires each order to have items. PostgreSQL, MySQL and SQLite all accept that form.

SQL Formatter Lay out a long join one table per line, with each ON below its table, before you review it.

Your turn

Exercise · Medium · SQL

Items bought and amount paid per customer, counted once

Four tables describe a shop's orders:

customers: customer_id and name;
orders: order_id and the customer_id who placed it;
order_items: order_id, product_id and quantity, one row per product in an order;
payments: payment_id, order_id and amount_paise. An order can have no payment yet, one payment, or several (a split payment), and two payments can have the same amount.

The starter query joins all four tables and sums, so it counts some items and payments more than once. Write a query that returns one row for every customer, with three columns:

customer_id;
items: the total quantity of all the items in the customer's orders;
paid_paise: the total amount_paise of all the payments for the customer's orders.

A customer without orders gets 0 in both columns, and orders without payments add 0 to paid_paise. The order of the rows does not matter.

Starter code · per_customer.sql

-- Items bought and amount paid per customer. This version double counts: fix it.
SELECT c.customer_id,
       sum(oi.quantity)     AS items,
       sum(pay.amount_paise) AS paid_paise
FROM customers AS c
JOIN orders AS o        ON o.customer_id = c.customer_id
JOIN order_items AS oi  ON oi.order_id   = o.order_id
JOIN payments AS pay    ON pay.order_id  = o.order_id
GROUP BY c.customer_id;

The sample tests · tests.yaml

# Sample tests: each one builds the four tables, runs your query on them and compares the rows it returns (in any
# order).
tests:
  - name: a split payment and several items are each counted once
    setup: |
      CREATE TABLE customers (customer_id INTEGER PRIMARY KEY, name TEXT);
      CREATE TABLE orders (order_id INTEGER PRIMARY KEY, customer_id INTEGER);
      CREATE TABLE order_items (order_id INTEGER, product_id INTEGER, quantity INTEGER, PRIMARY KEY (order_id, product_id));
      CREATE TABLE payments (payment_id INTEGER PRIMARY KEY, order_id INTEGER, amount_paise INTEGER);
      INSERT INTO customers VALUES (1, 'Ishita'), (2, 'Rohan');
      INSERT INTO orders VALUES (10, 1), (11, 2);
      INSERT INTO order_items VALUES (10, 1, 2), (10, 2, 1), (10, 3, 1), (11, 1, 5);
      INSERT INTO payments VALUES (1, 10, 500), (2, 10, 700), (3, 11, 900);
    expected:
      columns: [customer_id, items, paid_paise]
      rows:
        - [1, 4, 1200]
        - [2, 5, 900]
  - name: customers without orders, orders without payments and equal payments
    setup: |
      CREATE TABLE customers (customer_id INTEGER PRIMARY KEY, name TEXT);
      CREATE TABLE orders (order_id INTEGER PRIMARY KEY, customer_id INTEGER);
      CREATE TABLE order_items (order_id INTEGER, product_id INTEGER, quantity INTEGER, PRIMARY KEY (order_id, product_id));
      CREATE TABLE payments (payment_id INTEGER PRIMARY KEY, order_id INTEGER, amount_paise INTEGER);
      INSERT INTO customers VALUES (1, 'Farhan'), (2, 'Lakshmi'), (3, 'Gurpreet');
      INSERT INTO orders VALUES (20, 1), (21, 1), (22, 3);
      INSERT INTO order_items VALUES (20, 1, 2), (21, 4, 1), (22, 2, 1);
      INSERT INTO payments VALUES (1, 21, 300), (2, 22, 250), (3, 22, 250);
    expected:
      columns: [customer_id, items, paid_paise]
      rows:
        - [1, 3, 300]
        - [2, 0, 0]
        - [3, 1, 500]
  - name: several orders per customer, each with several items and payments
    setup: |
      CREATE TABLE customers (customer_id INTEGER PRIMARY KEY, name TEXT);
      CREATE TABLE orders (order_id INTEGER PRIMARY KEY, customer_id INTEGER);
      CREATE TABLE order_items (order_id INTEGER, product_id INTEGER, quantity INTEGER, PRIMARY KEY (order_id, product_id));
      CREATE TABLE payments (payment_id INTEGER PRIMARY KEY, order_id INTEGER, amount_paise INTEGER);
      INSERT INTO customers VALUES (1, 'Nandini'), (2, 'Kabir');
      INSERT INTO orders VALUES (30, 1), (31, 1), (32, 2);
      INSERT INTO order_items VALUES (30, 1, 1), (30, 2, 1), (30, 3, 1), (31, 4, 4), (32, 1, 2), (32, 5, 2);
      INSERT INTO payments VALUES (1, 30, 100), (2, 30, 100), (3, 30, 100), (4, 31, 50);
    expected:
      columns: [customer_id, items, paid_paise]
      rows:
        - [1, 7, 350]
        - [2, 4, 0]

A hint

Reduce each child table to one row per order before it meets the other: a query in parentheses such as (SELECT order_id, sum(quantity) AS items FROM order_items GROUP BY order_id) AS i can be joined like a table. Do the same for the payments, start from customers, and keep every join a LEFT JOIN so that customers without orders stay.

The sample tests run on this device, in your browser (sql.js): nothing is sent to mysmartcopilot.com. The first run downloads SQLite (about 0.7 MB), which is kept for the next runs. A check in your browser is feedback for you, not proof that the code is right for every input.

Interview questions

Fresher · What is fan-out in a join, and why does it make SUM too large?

Fan-out is the growth in rows when a join goes from a parent to its children: each parent row is repeated once for every matching child. It becomes a bug when one query joins two child tables of the same parent, such as orders to both their items and their payments. Every item of an order then meets every payment of that order, so an order with 3 items and 2 payments becomes 6 rows, and a sum of the payment amounts over them counts each payment 3 times. COUNT(DISTINCT order_id) still counts the orders correctly, which is why the bug hides, but SUM(DISTINCT amount) is wrong whenever two amounts are equal. The fix is to aggregate each child table to one row per parent first, then join the results.

Model answers open with any pass: ₹79 for 1 week ($2.99), one payment that never renews.

Choose a pass

Mid to senior

Finance says the "paid per customer" column of your dashboard adds up to 18 % more than the payments ledger. The query joins customers, orders, order_items and payments, then groups by customer and sums the payment amounts. How do you find the cause, fix it, and prove the fix?

Model answer, Pro. Model answers open with any pass; prices are in the notice on this page. First step and follow-ups
Fresher to mid

In a code review you see a double-counted total "fixed" by changing SUM(p.amount) to SUM(DISTINCT p.amount) and COUNT(*) to COUNT(DISTINCT o.order_id). The test data now matches. Do you approve it? What do you ask for instead?

Model answer, Pro. First step and follow-ups

Key takeaways

Say what one row of the result stands for, its grain, before you join.
A join to a parent keeps the row count; a join to children repeats each row once per child.
Two child tables of the same parent multiply each other, lines × payments rows per order, and sums over the result count the same line or payment more than once.
COUNT(DISTINCT …) can hide fan-out in counts; SUM(DISTINCT …) is wrong whenever two amounts are equal.
Reduce each child table to one row per parent in a derived table, or a WITH query, and join the results.
Compare count(*) with count(DISTINCT key), and every total with the same total over its own table.
An inner join after a LEFT JOIN drops the unmatched rows that the LEFT JOIN kept.

References

PostgreSQL 18: Table Expressions (joined tables and subqueries in FROM) (The PostgreSQL Global Development Group)
PostgreSQL 18: Value Expressions (aggregate expressions and DISTINCT) (The PostgreSQL Global Development Group)
SQLite: The SELECT statement (joins and subqueries in FROM) (SQLite)
SQLite: Built-in Aggregate Functions (DISTINCT in aggregates) (SQLite)
MySQL 8.4 Reference Manual: JOIN Clause (Oracle Corporation)
MySQL 8.4 Reference Manual: Derived Tables (Oracle Corporation)
MySQL 8.4 Reference Manual: Aggregate Function Descriptions (Oracle Corporation)

Report a problem with this lesson

In this module: Joining tables

Primary keys, foreign keys and relationships Free lesson, Pro answers: 20 minutes
INNER JOIN: combining rows from two tables Free lesson, Pro answers: 25 minutes
LEFT, RIGHT and FULL OUTER JOIN Free lesson, Pro answers: 30 minutes
Self-joins: comparing rows of one table Free lesson, Pro answers: 20 minutes
CROSS JOIN and combinations Free lesson, Pro answers: 20 minutes
Joining many tables without double counting Free lesson, Pro answers: 25 minutes
Range joins, as-of joins and LATERAL Free lesson, Pro answers: 30 minutes

Your country

Joining many tables without double counting

What you will learn

Before you start

What you will learn

Before you start

One row per what?

A four-table query, one join at a time

Fan-out: two child tables of the same orders

DISTINCT is not the fix

Aggregate before you join

Catch fan-out while you build

A LEFT JOIN undone by a later JOIN

Your turn

Items bought and amount paid per customer, counted once

Results of the sample tests

Check yourself

Interview questions

Finance says the "paid per customer" column of your dashboard adds up to 18 % more than the payments ledger. The query joins customers, orders, order_items and payments, then groups by customer and sums the payment amounts. How do you find the cause, fix it, and prove the fix?

In a code review you see a double-counted total "fixed" by changing SUM(p.amount) to SUM(DISTINCT p.amount) and COUNT(*) to COUNT(DISTINCT o.order_id). The test data now matches. Do you approve it? What do you ask for instead?

Key takeaways

References

Related tools