What a Join Produces Row-by-Row

Concepts covered: Join Cardinality, Nulls in Joins, Counting, Distinct Counting, Outer Joins

A join emits rows by a single rule: for every row arriving from the left, it outputs 1 row per matching row on the right. With exactly 1 match, the left row comes out once. With several matches, it comes out once per match, its own columns copied onto each. A left row with no match is dropped by INNER; LEFT emits it once with the right side's columns NULL. Every surprising row count traces back to one of those 3 cases.

The last row catches people. A NULL key never matches anything, including another NULL, because NULL = NULL evaluates to unknown and a join keeps only pairs whose condition is true. An order with a NULL customer_id never finds a customer. A LEFT chain anchored on orders still carries it forward with every customer column empty.

Watching 1 order become 2 rows

Here is the address hop isolated on Ana's order 1001, joined on customer_id alone. Predict it before pressing play. The order carries Ana's id, and customer_addresses holds both her shipping address and her billing address, so the predicted count is 2.

SELECT o.order_id, a.address_type, a.city
FROM orders o
JOIN customer_addresses a ON a.customer_id = o.customer_id

Order 1001 comes out once beside Portland and again beside Seattle, and nothing in the output marks the second row as a copy. That is what makes a repeated row dangerous: it looks exactly like data. A report built on this result lists Ana's order twice, and a SUM of amount over it counts her $40 twice. Adding a.address_type = 'shipping' to the ON clause gives Ana 1 partner and the count falls back to 1.

Predicting the count hop by hop

The full running query starts at customers and makes 3 hops. Each hop's output is the sum, over its input rows, of the match count, where a LEFT hop counts an unmatched row as 1 and an INNER hop counts it as 0. On 4 customers the arithmetic takes seconds, and interviewers ask for it more often than candidates expect.

The final 4 matches the request's grain: 1 row for each of the 3 orders and 1 for Dev, who has none. A predicted count that matches the grain is the strongest evidence of correctness available before running anything. When prediction and result disagree, the first hop where they part ways is the bug, which turns a wrong total into a search across 3 lines instead of the whole query.

-- Grain check: each order once, plus 1 row per customer with no order
SELECT COUNT(*)                     AS total_rows,
       COUNT(DISTINCT o.order_id)   AS distinct_orders,
       COUNT(*) - COUNT(o.order_id) AS rows_without_order
FROM customers c
LEFT JOIN orders o             ON o.customer_id = c.customer_id
LEFT JOIN products p           ON p.product_id  = o.product_id
LEFT JOIN customer_addresses a ON a.customer_id = c.customer_id
                              AND a.address_type = 'shipping';
-- 4 | 3 | 1: total equals distinct orders plus orderless rows, so nothing repeated

Drop the address_type condition from that query and it returns 5 alongside 3 and 1: 5 rows against 4 accounted for, and the extra row is Ana's order repeated by her billing address. Read together, the 3 numbers from one query tell you that a repeat exists and roughly where to look.

Where rows go missing

Lost rows are quieter than repeated ones, because every row that remains looks right and the result is only shorter than it should be. On this schema they come from 3 places: an INNER hop to a table missing the partner row, like order 1003 against a catalog with no product 13; a NULL foreign key, which matches nothing; and an INNER hop placed after a LEFT one, which filters out the rows the LEFT preserved. Each shows up as an actual count below the predicted one, which is why the prediction has to be written down first.

About this lesson

This section is part of the Multi-Table Joins: Beginner lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.