When a Join Gives Too Many Rows or Too Few

A join that fails loudly is easy. A join that succeeds with the wrong number of rows is how reports end up wrong for months. The result looks like data, it adds up to something, and nobody checks. This lesson is about the two ways it goes wrong, how to see it, and how to fix it.

The habit that catches most of it costs one line. Count the rows before and after every join, and know why the number changed.

Too few rows: the join that dropped something

Start with a number you can check. There are eight customers.

SELECT count(*) AS customers FROM customers;

 customers
-----------
         8
(1 row)

Join them to their orders with an INNER JOIN and count the customers you still have.

SELECT count(DISTINCT c.customer_id) AS customers_with_orders
FROM customers AS c
INNER JOIN orders AS o ON o.customer_id = c.customer_id;

 customers_with_orders
-----------------------
                     6
(1 row)

Two are gone. Dan Lim and Gina Ortiz have no orders, so an INNER JOIN has nothing to match them with. If the question was "orders with a customer name", that is right. If the question was "every customer and how many orders they have", you wanted a LEFT JOIN.

There is a quieter way to lose rows. You use a LEFT JOIN, then filter on the right table in WHERE.

SELECT c.name, o.order_id
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.customer_id
WHERE o.status = 'paid'
ORDER BY c.name;

    name    | order_id
------------+----------
 Ana Reyes  |        2
 Carla Diaz |        6
 Finn Go    |        9
 Hugo Sy    |       12
(4 rows)

Four customers survived, even though it is a LEFT JOIN. The unmatched rows have status NULL, and NULL = 'paid' is unknown, so WHERE drops them. The LEFT JOIN turned back into an INNER JOIN. If you want every customer and only their paid orders, move the condition into ON.

SELECT c.name, o.order_id
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.customer_id AND o.status = 'paid'
ORDER BY c.name;

    name    | order_id
------------+----------
 Ana Reyes  |        2
 Ben Cruz   |
 Carla Diaz |        6
 Dan Lim    |
 Ella Tan   |
 Finn Go    |        9
 Gina Ortiz |
 Hugo Sy    |       12
(8 rows)

All eight customers are there. ON decides which right-hand rows match. WHERE decides which result rows survive. For an INNER JOIN the two behave the same. For a LEFT JOIN they do not.

Too many rows: the fan-out

Orders and their items are one-to-many. Join them and each order is repeated once per item.

SELECT count(*) AS orders FROM orders;

 orders
--------
     12
(1 row)

SELECT count(*) AS joined_rows, count(DISTINCT o.order_id) AS distinct_orders
FROM orders AS o
INNER JOIN order_items AS i ON i.order_id = o.order_id;

 joined_rows | distinct_orders
-------------+-----------------
          20 |              12
(1 row)

There are 12 orders and 20 joined rows, because several orders have more than one item. That is a fan-out. It is correct for listing items. It is wrong the moment you count or add something that belongs to the order, because it is counted once per item. Count what you mean: count(DISTINCT o.order_id) counts orders, count(*) counts item rows.

The same trap in money form: sum a value that lives on the one side after joining the many side, and it is added once per row. If you need a total per order, aggregate the many side first or sum only the columns that belong to it.

Too many rows: the join with no rule

Forget the ON condition, or write a comma-join and forget the WHERE, and every row meets every row.

SELECT count(*) AS rows_from_cross_join
FROM customers CROSS JOIN orders;

 rows_from_cross_join
----------------------
                   96
(1 row)

Eight customers times twelve orders is 96 rows. PostgreSQL does not stop you, because sometimes you want every combination. With a big table the result would be millions of rows, so a missing ON is one of the slowest mistakes to make.

The old comma form is the same thing with less on screen.

SELECT count(*) AS rows_from_comma_join FROM customers, orders;

 rows_from_comma_join
----------------------
                   96
(1 row)

A way to debug any join

When a join result looks wrong, check these in order.

1. Count the left table on its own.
2. Add one join and count again. Did it go down or up? Which one did you expect?
3. Check that the column you join on is unique on the side you think is "one".

SELECT customer_id, count(*) AS orders
FROM orders
GROUP BY customer_id
HAVING count(*) > 1
ORDER BY customer_id;

 customer_id | orders
-------------+--------
           1 |      3
           2 |      2
           3 |      3
           5 |      2
(4 rows)

If that returns rows, customer_id is not unique in orders. Joining customers to orders will repeat each of those customers once per order, which is correct here, and a mistake if you assumed one row per customer.

Common mistakes

• Using INNER JOIN where the question needs LEFT JOIN, so rows silently vanish.
• Filtering the right table in WHERE after a LEFT JOIN.
• Counting or summing across a one-to-many join without checking the row count.
• Joining on a column that is not unique when you think it is.
• Leaving out the ON condition.

If you skip this, you will ship a number that is wrong by a factor you never see. What is the row count of the last join you wrote, and do you know why?