Multiplicity and missing rows

What this concept is

A query contract includes which entities appear, how often they appear, which values are aggregated and how ties are resolved. SQL results can retain duplicates even when the conceptual relational model is described using sets. A join constructs intermediate rows; an aggregate operates on those rows.

Why it matters here

The report requires every customer, including a customer with no paid orders. Joining only paid orders must preserve unmatched customers. Counting the non-null order key measures matches; counting every intermediate row also counts the outer join’s placeholder.

Concrete example

Customer 1 has two distinct paid orders of 7 cents each; customer 2 has only an open order; customer 3 has no orders. The paid report must be (1,2,14), (2,0,0), (3,0,0). Put the paid predicate in the LEFT JOIN condition. COUNT(order_id) ignores the unmatched null key; COALESCE converts the missing sum to zero. SUM(DISTINCT cents) wrongly changes 14 to 7.

Common confusion / misconception

Moving the paid predicate from ON to WHERE removes the unmatched rows, because their status comparison is not true. Adding DISTINCT to an inflated report can hide a join error rather than repair it. If orders join to multiple items, first establish the intended aggregate grain instead of summing the repeated order totals.

How to use it

Write the result grain before SQL: one row per customer ID. Draw each intermediate joined row for a small fixture. Group by the identifier rather than a display name; different customers may share names. For top-one paid order, explicitly order by cents descending and order ID ascending inside ROW_NUMBER, then order the final output separately.

Check yourself

Would replacing ROW_NUMBER with RANK preserve exactly one result when two amounts tie? Which clause determines output order, and does a window order alone establish it?

Mini drill or application

Add a second customer named identically to the first, then add two equal-price orders and a second item per order. Predict a correct customer report and the output of the naive join before executing either.

Read this only if stuck

Use Database System Concepts on joins and aggregates, then SQLite SELECT for the exact execution semantics used by this lab. Stop when you can reconstruct the three-customer example without notes.