SQL problem

Why did my JOIN duplicate rows?

Nothing in your tables was duplicated. A JOIN emits one row for every pair of rows that satisfies the ON condition, and one of your left rows has more than one partner. Here is the smallest version of that, with the output it produces and the total it breaks.

The symptom

Two separate things happened in that one query, and the row count hid both of them. One order was matched twice because it has two shipments. One order disappeared because it has none. The totals that come out of that join are the part that reaches a dashboard.

The two tables

One order can own many shipments. orders.order_id identifies an order and appears once. shipments.order_id points at that identity and is allowed to repeat, which it does: order 801 went out in two boxes. That permission to repeat is the entire mechanism behind the extra rows.

The query and what it returns

SELECT o.order_id, o.total, s.carrier
FROM orders o
JOIN shipments s ON s.order_id = o.order_id
ORDER BY o.order_id, s.shipment_id;

Which left row matched which right rows

• orders 801 matched shipments 9001 and 9002. Two matching pairs, so two output rows, and both of them carry the same total of 125.
• orders 802 matched shipment 9003. One pair, one output row.
• orders 803 matched nothing. A plain JOIN has no pair to emit, so order 803 is absent from the output entirely.

Read the output as a list of pairs and the count stops being surprising. Three input orders and three output rows here is a coincidence of the data. The output row count is the number of matching pairs, and it has no obligation to equal the row count of either table.

The number that actually breaks

SELECT SUM(o.total) AS revenue
FROM orders o
JOIN shipments s ON s.order_id = o.order_id;

Orders 801 and 802 are worth 205 between them. The query reports 330, because the 125 was counted once per shipment. Duplicate rows on screen are easy to notice. A sum that is 125 too high looks like a normal number, and no error is raised.

Fix 1: collapse the many side before you join

If you want a shipment fact next to each order, reduce shipments to one row per order first, then join that. The order rows cannot multiply against something that has one row per order.

SELECT o.order_id, o.total, s.shipment_count
FROM orders o
LEFT JOIN (
  SELECT order_id, COUNT(*) AS shipment_count
  FROM shipments
  GROUP BY order_id
) s ON s.order_id = o.order_id
ORDER BY o.order_id;

Three orders, three rows, one row each. SUM(o.total) over this join returns 505, which is every order in the table, because the LEFT JOIN kept order 803 and padded its right-hand column with NULL. Before you write an ON clause, write the phrase "one row per ..." beside each side. When the two phrases differ, the finer side is the one that multiplies.

Fix 2: EXISTS, when you wanted no columns from the other table

The question "which orders shipped" never needed a carrier or a shipment id. A semi-join asks whether a match exists and stops there, so a second shipment cannot add a row.

SELECT o.order_id, o.total
FROM orders o
WHERE EXISTS (SELECT 1 FROM shipments s WHERE s.order_id = o.order_id)
ORDER BY o.order_id;

Fix 3: DISTINCT, and the case where it lies

SELECT DISTINCT o.order_id, o.total
FROM orders o
JOIN shipments s ON s.order_id = o.order_id
ORDER BY o.order_id;

That returns the same two rows as the EXISTS version, 801 with 125 and 802 with 80, and summing total over it gives 205. It works because order_id is in the select list, so two different orders stay two different rows even when everything else about them matches.

Take the key out and it stops working silently. Give order 802 a total of 125 as well, so two genuinely different orders carry the same number, and SELECT DISTINCT o.total collapses them into one row: the sum reads 125 where the right answer is 250. DISTINCT is safe over a join when the row identity you are counting is in the select list, and unsafe the moment it is not.

Check yourself

Order 802 also goes out in two boxes now, so shipments gains one row: (9004, 802, 'ups'). Before running anything, answer both parts. How many rows does the first query on this page return, and what does SUM(o.total) report for that join?

Dialect note

Fan-out is part of how a join is defined, so every engine here behaves the same way. Every statement and every result table on this page was executed before publishing: on DuckDB 1.5.3, the engine family the game uses to grade queries in your browser, and again on SQLite 3.53, which returned identical rows. Postgres and MySQL 8 follow the same rules for matching pairs, for EXISTS and for DISTINCT.

One syntax difference is worth keeping in your fingers: Postgres and MySQL both require a name on a derived table, so the ) s at the end of the subquery in fix 1 is mandatory there. DuckDB and SQLite accept the subquery without one. Writing the alias always is the portable habit.

The Steam edition launches on Nov 12 with the whole course, holographic reward cards and a title-screen parallax. Wishlist on Steam