My SQL JOIN Ran Perfectly and Doubled the Refunds

The SQL query ran without errors. The columns looked right. The totals seemed reasonable.

Except Ava's refund total was $60.

She'd only been refunded $30.

No missing data. No syntax error. Nothing obviously wrong with the query.

SQL had done exactly what the query asked it to do. The problem was that the query wasn't asking the right question.

Here's the idea that explains it:

A JOIN doesn't guarantee one result row per row in your first table. One row can match multiple rows, and those matches can multiply your results.

That might sound harmless until you start calculating totals.

Let's reproduce the problem with a dataset small enough to check every number ourselves.

Step 1: Four customers, six orders, three refunds

Imagine we're building a report for an online store.

We have three tables: customers, orders, and refunds.

Let's create them.

CREATE TABLE customers (
    customer_id INTEGER PRIMARY KEY,
    name        TEXT
);

CREATE TABLE orders (
    order_id    INTEGER PRIMARY KEY,
    customer_id INTEGER,
    amount      INTEGER
);

CREATE TABLE refunds (
    refund_id   INTEGER PRIMARY KEY,
    customer_id INTEGER,
    amount      INTEGER
);

INSERT INTO customers VALUES
    (1, 'Ava'),
    (2, 'Ben'),
    (3, 'Cara'),
    (4, 'Dan');

INSERT INTO orders VALUES
    (101, 1, 90),
    (102, 1, 40),
    (103, 2, 75),
    (104, 2, 60),
    (105, 2, 60),
    (106, 3, 120);

INSERT INTO refunds VALUES
    (1, 1, 20),
    (2, 1, 10),
    (3, 2, 15);

For this example, orders and refunds are recorded separately against each customer.

Here's what our actual numbers look like:

Customer	Orders	Total spent	Total refunded
Ava	2	130	30
Ben	3	195	15
Cara	1	120	0
Dan	0	0	0
Total	6	445	45

These are the correct numbers.

Keep them in mind. We're about to make SQL return something very different.

Step 2: One JOIN, more rows than customers

Let's start with something simple.

SELECT
    c.name,
    o.order_id,
    o.amount
FROM customers AS c
JOIN orders AS o
    ON o.customer_id = c.customer_id
ORDER BY o.order_id;

Result:

name	order_id	amount
Ava	101	90
Ava	102	40
Ben	103	75
Ben	104	60
Ben	105	60
Cara	106	120

Four customers. Six joined rows.

Ava appears twice because she has two orders. Ben appears three times because he has three.

Dan disappears because an inner JOIN only returns matching rows, and Dan has no orders.

SQL hasn't accidentally duplicated anything.

Each result row represents one matching customer-order pair.

And that brings us to a concept that's extremely useful in analytics.

Grain.

Grain simply means what one row represents.

In our example:

• customers has one row per customer.
• orders has one row per order.
• The joined result has one row per matching order, with customer information attached.

The relationship between customers and orders is one-to-many.

One customer can have multiple orders.

You'll see the same pattern everywhere. Departments have employees. Accounts have transactions. Projects have tasks.

Joining to the many side can change the grain of your result.

That's not inherently a problem. Sometimes that's exactly what you want.

The trouble starts when you forget it happened.

Step 3: The report that's only half right

Now imagine we want a customer report showing how much each customer spent and how much they received in refunds.

This query looks reasonable:

SELECT
    c.name,
    SUM(o.amount) AS total_spent,
    SUM(r.amount) AS total_refunded
FROM customers AS c
LEFT JOIN orders AS o
    ON o.customer_id = c.customer_id
LEFT JOIN refunds AS r
    ON r.customer_id = c.customer_id
GROUP BY
    c.customer_id,
    c.name
ORDER BY c.customer_id;

It runs successfully.

Here's what we get:

name	total_spent	total_refunded
Ava	260	60
Ben	195	45
Cara	120	NULL
Dan	NULL	NULL

Something is wrong.

Ava actually spent $130, but the report says $260. Her refunds doubled from $30 to $60.

Ben's spending is correct at $195, but his $15 refund has somehow become $45.

Cara looks fine, apart from the missing refund being represented as NULL.

And Dan has no activity.

Across the whole report, the spending total has grown from $445 to $575, and refunds have jumped from $45 to $105.

The dangerous part?

Some values are correct. Others are wrong. And every incorrect number looks believable.

That's what makes this kind of SQL bug difficult to catch.

Step 4: Why it happens

Look at our joins again.

LEFT JOIN orders AS o
    ON o.customer_id = c.customer_id

LEFT JOIN refunds AS r
    ON r.customer_id = c.customer_id

Both tables are joined using customer_id.

But neither orders nor refunds necessarily contains only one row per customer.

Ava has two orders and two refunds.

So SQL produces every matching order-refund combination for Ava.

order_id	order_amount	refund_id	refund_amount
101	90	1	20
101	90	2	10
102	40	1	20
102	40	2	10

Two orders multiplied by two refunds gives us four rows.

Now look at what happens when we aggregate:

Order total:
90 + 90 + 40 + 40 = 260

Refund total:
20 + 10 + 20 + 10 = 60

Every order was counted twice because there were two matching refunds.

Every refund was counted twice because there were two matching orders.

This is called join fan-out.

Now consider Ben.

He has three orders but only one refund.

Three orders multiplied by one refund gives us three rows.

His order amounts each appear once, so his spending total stays correct.

But his single $15 refund appears three times.

That's why the query returns $45.

The important thing to understand is:

When you join two one-to-many relationships through the same key, matching rows from each side can combine with every matching row from the other side.

And a LEFT JOIN doesn't prevent that.

A left join preserves unmatched rows from the left side. It doesn't guarantee only one match for each row.

Step 5: How to catch this before trusting the report

When a JOIN produces unexpected totals, the first thing I'd check isn't the SUM().

I'd check what the rows represent.

Start with two questions:

What should one row in the final report represent?

In this case, one customer.

Can the join key match multiple rows in either table?

Let's find out.

Check 1: Count the rows before aggregation

SELECT
    c.name,
    COUNT(*) AS joined_rows
FROM customers AS c
LEFT JOIN orders AS o
    ON o.customer_id = c.customer_id
LEFT JOIN refunds AS r
    ON r.customer_id = c.customer_id
GROUP BY
    c.customer_id,
    c.name
ORDER BY c.customer_id;

Result:

name	joined_rows
Ava	4
Ben	3
Cara	1
Dan	1

This tells us how many joined rows feed into each customer's calculations.

Ava's totals are being calculated from four rows, not two orders or two refunds independently.

Notice that the original report still returned one final row per customer because of GROUP BY.

Having the correct number of final rows doesn't mean your aggregated values are correct.

Check 2: Look for repeated join keys

For example, let's check the refunds table.

SELECT
    customer_id,
    COUNT(*) AS rows_per_key
FROM refunds
GROUP BY customer_id
HAVING COUNT(*) > 1;

Result:

customer_id	rows_per_key
1	2

Customer 1 has two refund rows.

That means a customer-level JOIN can multiply rows for Ava.

We should perform the same uniqueness check on orders, because that table also has multiple rows per customer.

Check 3: Reconcile totals with the source

This is one of the simplest checks you can make.

SELECT
    SUM(amount) AS actual_refunds
FROM refunds;

Result:

actual_refunds
--------------
45

But our customer report adds up to 105.

If both reports cover the same records and use the same definitions, those totals should agree.

They don't.

That tells us the aggregation logic needs attention.

Step 6: Fix the grain before joining

Here's the solution.

We want one row per customer.

So rather than joining individual orders and refunds directly, let's aggregate each table to one row per customer first.

WITH order_totals AS (
    SELECT
        customer_id,
        COUNT(*) AS order_count,
        SUM(amount) AS total_spent
    FROM orders
    GROUP BY customer_id
),
refund_totals AS (
    SELECT
        customer_id,
        SUM(amount) AS total_refunded
    FROM refunds
    GROUP BY customer_id
)
SELECT
    c.name,
    COALESCE(o.order_count, 0) AS order_count,
    COALESCE(o.total_spent, 0) AS total_spent,
    COALESCE(r.total_refunded, 0) AS total_refunded
FROM customers AS c
LEFT JOIN order_totals AS o
    ON o.customer_id = c.customer_id
LEFT JOIN refund_totals AS r
    ON r.customer_id = c.customer_id
ORDER BY c.customer_id;

Now the results are correct:

name	order_count	total_spent	total_refunded
Ava	2	130	30
Ben	3	195	15
Cara	1	120	0
Dan	0	0	0

Every number matches our original data.

Why does this work?

Because each CTE has at most one row per customer_id.

The order_totals CTE summarizes orders independently.

The refund_totals CTE summarizes refunds independently.

Then we join those customer-level summaries to the customer table.

There's no order-refund multiplication left to inflate our totals.

We also keep LEFT JOIN so Dan still appears, even without orders or refunds.

And COALESCE turns missing aggregate values into zero for this report.

The key takeaway:

If your report needs one row per customer, make sure the data you're joining is at the customer grain before calculating customer-level totals.

That's often much safer than joining raw transactions and trying to repair the result afterward.

The tempting fix: DISTINCT

Here's where things get interesting.

When people see repeated rows, a common reaction is to use DISTINCT.

So what if we try this?

SELECT
    c.name,
    SUM(DISTINCT o.amount) AS total_spent,
    SUM(DISTINCT r.amount) AS total_refunded
FROM customers AS c
LEFT JOIN orders AS o
    ON o.customer_id = c.customer_id
LEFT JOIN refunds AS r
    ON r.customer_id = c.customer_id
GROUP BY
    c.customer_id,
    c.name
ORDER BY c.customer_id;

Result:

name	total_spent	total_refunded
Ava	130	30
Ben	135	15
Cara	120	NULL
Dan	NULL	NULL

Ava is fixed.

Ben is now wrong in a completely different way.

His actual spending was $195, but the query says $135.

Why?

Look at Ben's orders:

Order 103: $75
Order 104: $60
Order 105: $60

Actual total: $195

Ben placed two separate $60 orders.

But SUM(DISTINCT o.amount) doesn't know that those are different orders.

It only sees the values.

So it calculates:

75 + 60 = 135

One legitimate $60 order disappears from the calculation.

This is why SUM(DISTINCT ...) is not a general fix for JOIN fan-out.

DISTINCT removes duplicate values, not repeated business entities.

There's nothing wrong with using DISTINCT when you genuinely need unique values.

But using it to cover up unexpected join multiplication can replace one reporting error with another.

Fix the grain, not just the symptom.

Two more traps worth knowing

COUNT(*) can mislead you after a LEFT JOIN

Remember Dan?

He has no orders.

Now run this:

SELECT
    c.name,
    COUNT(*) AS joined_rows,
    COUNT(o.order_id) AS order_count
FROM customers AS c
LEFT JOIN orders AS o
    ON o.customer_id = c.customer_id
GROUP BY
    c.customer_id,
    c.name
ORDER BY c.customer_id;

Result:

name	joined_rows	order_count
Ava	2	2
Ben	3	3
Cara	1	1
Dan	1	0

Dan has zero orders.

But COUNT(*) returns 1 because the LEFT JOIN preserves an unmatched customer row.

The order columns in that row are NULL.

COUNT(o.order_id) ignores those NULLs and correctly returns zero.

One caution: when you add other one-to-many joins, even COUNT(o.order_id) can become inflated by fan-out. Count at the correct grain or count distinct identifiers when that matches the question.

Sometimes EXISTS is better than JOIN

Suppose we only want to answer:

"Which customers have received at least one refund?"

We don't need every refund row.

We just need to know whether one exists.

SELECT
    c.name
FROM customers AS c
WHERE EXISTS (
    SELECT 1
    FROM refunds AS r
    WHERE r.customer_id = c.customer_id
)
ORDER BY c.customer_id;

Result:

Ava
Ben

Each customer appears once.

An ordinary inner JOIN to refunds would return Ava twice because she has two refunds.

EXISTS answers the existence question without bringing every matching refund into the result.

That's a useful distinction.

The checklist I use before trusting a JOIN

Before trusting a query that calculates counts, totals, or averages, these are the questions worth asking:

1. What's the grain of each table? What does one source row represent?
2. What grain should my result have? One row per customer, order, transaction, or something else?
3. Is the join key unique on either side? If not, how many matches can each row produce?
4. Am I combining multiple one-to-many relationships? If so, should I aggregate each source separately before joining?
5. Do the totals reconcile with the source data? Can I explain any differences?

Most JOIN tutorials focus on INNER JOIN, LEFT JOIN, and RIGHT JOIN.

Those are important.

But there's another question that's just as useful:

How many rows can each row match?

Once you start asking that, unexpected JOIN results become much easier to understand.

And you stop assuming that a query is correct just because it runs successfully.

Try it yourself

Take the example tables from this article and deliberately break the report.

Add another refund for Ava. Predict how many joined rows she'll have and how much her spending total will increase.

Then add a second order for Cara and compare the results.

Finally, try adding a customer with no orders or refunds and see how INNER JOIN, LEFT JOIN, and EXISTS behave.

I'm building Runtime, a free, hands-on learning platform for SQL and Python where you can learn concepts by writing and running actual queries.

Working with small datasets like these is one of the best ways to understand what SQL is really doing before you run the same logic against millions of records.

What's the most confusing JOIN bug you've encountered?