Investigate: why did customers get the email three times?

The report

Marketing sent a thank-you email to every customer with a paid or shipped order. Several customers replied asking why they got it more than once. The list came from this query:

SELECT c.email
FROM customers c
JOIN orders o ON o.customer_id = c.id
WHERE o.status IN ('paid', 'shipped');

Your job: find out why, prove it with numbers, and fix the query.

1. Reproduce it

Run the query on shop, sorted so repeats sit together (ORDER BY c.email):

       email
--------------------
 asha@example.com
 asha@example.com
 asha@example.com
 ben@example.com
 chen@example.com
 divya@example.com
 farhan@example.com
 farhan@example.com
 grace@example.com
(9 rows)

Asha is there three times, Farhan twice.

2. Measure it

Count rows against distinct customers:

SELECT count(*) AS emails_sent, count(DISTINCT c.email) AS customers
FROM customers c
JOIN orders o ON o.customer_id = c.id
WHERE o.status IN ('paid', 'shipped');

	Ours	Yours
Rows returned (emails sent)	9
Distinct customers	6

3. Explain it

Look at who repeats, and compare with their orders:

SELECT customer_id, count(*) FROM orders
WHERE status IN ('paid', 'shipped')
GROUP BY customer_id ORDER BY customer_id;

Asha has three paid or shipped orders (101, 103, 108) and Farhan two (107, 112). An inner join returns one row for every matching pair of customer and order. The query asked for customers but walked through orders, so each customer came out once per qualifying order.

This is called join fan-out: joining a “one” table to a “many” table multiplies the “one” side’s rows. It is not an error, and nothing warns you. It only shows up as wrong counts, doubled totals or repeated emails.

4. Fix it

The question was “which customers have at least one paid or shipped order?”, which is a semi-join:

SELECT c.email
FROM customers c
WHERE EXISTS (
  SELECT 1 FROM orders o
  WHERE o.customer_id = c.id AND o.status IN ('paid', 'shipped')
)
ORDER BY c.email;

Six rows, one per customer.

SELECT DISTINCT c.email … JOIN … also returns six rows here. It treats the symptom: it builds all nine rows and then removes duplicates. As soon as the query also needs a column that differs between those rows, such as an order date, DISTINCT stops hiding the repeats. EXISTS asks the right question, so there is nothing to hide.

Query	Rows (ours)	Rows (yours)
Original join	9
EXISTS	6

Check yourself

1. Why did joining customers to orders repeat customers?

   Show answer

   An inner join returns one row per matching customer–order pair. A customer with three qualifying orders matches three times.

   I could explain this without looking

2. How can you detect fan-out in a result?

   Show answer

   Compare count(*) with count(DISTINCT key) for the entity you expect one row of, such as the customer’s id or email. If they differ, rows are being multiplied.

   I could explain this without looking

3. Why is EXISTS a better fix than DISTINCT here?

   Show answer

   EXISTS asks the actual question (does at least one qualifying order exist?) and returns one row per customer by construction. DISTINCT removes duplicates afterwards, and stops working once the selected columns differ between the repeated rows.

   I could explain this without looking