Skip to course content
Free SQL course

SQL for Data Analysis and AI

Unit 05.01: Predicting row count before you run the join

Unit ID: SQL-M05-U02 - Estimated active time: 13-16 minutes Objective: state the expected row count from the relationship shape, and treat a surprise as information.

The prediction is the check

Three join shapes and the row count each one produces A one-to-one join, such as orders to a hypothetical one-row-per-order shipment table, leaves the row count unchanged at 1,000. A one-to-many join, such as orders to order_items, multiplies rows: 1,000 orders become 3,400 rows, one per line item. A many-to-many join multiplies both sides and produces more rows than either table, which is almost always a mistake. Predict the shape before running the join, then confirm it with a row count. Predict the row count before you run the join one - to - one one order, one shipment 1,000 → 1,000 grain unchanged one - to - many one order, several items 1,000 → 3,400 grain changes — totals inflate many - to - many every match pairs with every match more than either side almost always a mistake Write down the count you expect, run the join, then compare. A surprise means the grain moved.
Predict the shape, then confirm it with a row count before and after the join.

Before running any join, answer: for one row on the left, how many rows can match on the right?

RelationshipExpected result
one-to-onesame row count as the left table
one-to-manygrows to the many side
many-to-manymultiplies

Our tables:

SELECT (SELECT COUNT(*) FROM orders)      AS orders,        -- 1000
       (SELECT COUNT(*) FROM order_items) AS order_items;   -- 3400

One order has 3 or 4 items. So joining orders to order_items is one-to-many, and the prediction is 3,400 rows - the item grain, not the order grain.

SELECT COUNT(*) FROM orders o
JOIN order_items i ON i.order_id = o.order_id;
-- 3400

Prediction met. The join is behaving as expected, and you now know the result is at item grain.

When the count surprises you

A count you did not predict is not a nuisance - it is a finding. Three explanations, in order of likelihood:

  1. The relationship is not what you assumed. You expected one-to-one and it is one-to-many.
  2. The join key is not unique on a side you assumed it was.
  3. The join condition is incomplete - a composite key needs both columns.

Each is worth knowing before you aggregate anything.

Payments: a second one-to-many

SELECT COUNT(*) FROM payments;                                  -- 1185
SELECT COUNT(*) FROM orders o JOIN payments p ON p.order_id = o.order_id;  -- 1185

988 completed orders produced 1,185 payments, because every fifth completed order was paid in two instalments. That is 197 orders with a second payment:

SELECT COUNT(*) FROM (
  SELECT order_id FROM payments GROUP BY order_id HAVING COUNT(*) > 1
);
-- 197

988 + 197 = 1,185. The arithmetic checks out, which is exactly the kind of reconciliation Module 10 formalises.

Practice

Predict the row count of customers JOIN orders, then run it and explain any difference from 4,812.

Check your answer 
SELECT COUNT(*) FROM customers c JOIN orders o ON o.customer_id = c.customer_id;
-- 1000

The prediction "at least 4,812" would be wrong, because this is an inner join and 3,812 customers have no orders. The relationship is one-to-many, but the many side is often zero - and an inner join drops the zero cases. Shape alone is not enough; you also need to know whether the many side can be empty.

Takeaway

Predict the count from the relationship, then compare. A match confirms your model of the data; a mismatch teaches you something about it.

---