Join Cardinality and Fan-out

What Is Fan-out

Fan-out occurs when a join produces more rows than expected because the join key is not unique on one or both sides. If table A has 3 rows with customer_id = 1 and table B has 4 rows with customer_id = 1, joining on customer_id produces 3 × 4 = 12 rows for that single key value. This is the Cartesian product problem applied to a subset of the data.

Why It Matters

Fan-out does not produce an error. The query runs successfully and returns a result set. But every aggregate computed over that result set — SUM, COUNT, AVG — is wrong. Revenue is inflated. Counts are inflated. Averages are skewed. The query is syntactically correct and semantically wrong, which makes fan-out one of the most dangerous bugs in analytical SQL.

A Concrete Example

-- orders: one row per order
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    amount DECIMAL(10,2)
);

-- order_items: one row per item within an order
CREATE TABLE order_items (
    item_id INT PRIMARY KEY,
    order_id INT,
    product_name VARCHAR(100),
    quantity INT,
    price DECIMAL(10,2)
);

Order 101 has 3 items. Order 102 has 2 items. If you join orders to order_items on order_id, you get 5 rows — one per item. That is correct. But if you then join that result to a discounts table where order_id is not unique (multiple discounts per order), the row count explodes.

The Cardinality Check

Before writing a join, ask: What is the cardinality of the join key on each side?

• One-to-one (1:1): The key is unique on both sides. The join produces at most max(|A|, |B|) rows. Safe.
• One-to-many (1:N): The key is unique on one side, duplicated on the other. The join produces more rows than the "one" side. Expected and manageable.
• Many-to-many (M:N): The key is duplicated on both sides. The join produces a multiplicative explosion. Dangerous.

The cardinality is determined by the data model, not the query. A well-designed schema documents these relationships. When you encounter an unfamiliar table, check for unique constraints or run a quick diagnostic:

-- Check if order_id is unique in order_items
SELECT order_id, COUNT(*) AS cnt
FROM order_items
GROUP BY order_id
HAVING COUNT(*) > 1
LIMIT 10;

If this returns rows, order_id is not unique in order_items, and joining on it will fan out.

The Symptom

You write a query that joins a fact table to a dimension table and computes SUM(revenue). The number is 3x larger than the finance team's report. The query has no syntax error. The join condition looks correct. What went wrong?

Step 1: Check Row Counts

The first diagnostic is always a row count comparison:

-- Count rows before the join
SELECT COUNT(*) FROM orders;
-- Result: 10,000

-- Count rows after the join
SELECT COUNT(*)
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id;
-- Result: 23,000

If the post-join count is larger than the pre-join count of the table you are aggregating, you have fan-out. The join is multiplying rows.

Step 2: Identify the Duplicated Key

Find which join key is causing the multiplication:

-- Check for duplicate order_ids in order_items
SELECT order_id, COUNT(*) AS item_count
FROM order_items
GROUP BY order_id
ORDER BY item_count DESC
LIMIT 10;

If some order_id values appear multiple times, that is your fan-out source.

Step 3: Verify the Aggregation Impact

Compare the inflated aggregate to the correct value:

-- Inflated: sums order-level revenue once per item row
SELECT SUM(o.amount)
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id;

-- Correct: aggregate orders separately
SELECT SUM(amount) FROM orders;

The inflated sum will be larger because each order's amount is counted once for every matching item row.

The General Diagnostic Pattern

Whenever you add a join to a working query, run these three checks:

1. Row count before: SELECT COUNT(*) FROM main_table
2. Row count after: SELECT COUNT(*) FROM main_table JOIN other_table ON ...
3. Ratio analysis: If the ratio is not 1:1 (or the expected 1:N ratio), investigate the join key's cardinality.

A Real-World Scenario

A marketing analyst joins campaigns to ad_spend on campaign_id and computes total spend per campaign. The result is 5x the expected value. Investigation reveals that ad_spend has one row per day per campaign, and the analyst did not account for the daily granularity. The fix is to pre-aggregate ad_spend to the campaign level before joining:

SELECT c.campaign_name, s.total_spend
FROM campaigns c
JOIN (
    SELECT campaign_id, SUM(daily_spend) AS total_spend
    FROM ad_spend
    GROUP BY campaign_id
) s ON c.campaign_id = s.campaign_id;

The subquery collapses ad_spend to one row per campaign before the join, eliminating the fan-out.

Strategy 1: Pre-aggregate Before Joining

The most reliable fix for fan-out is to aggregate the many-side table to the join key's granularity before joining. This guarantees one row per join key value.

-- Instead of joining directly (causes fan-out)
SELECT c.name, SUM(o.amount)
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
JOIN order_items oi ON o.order_id = oi.order_id  -- fans out
GROUP BY c.name;

-- Pre-aggregate order_items to order level first
SELECT c.name, SUM(o.amount)
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
JOIN (
    SELECT order_id, SUM(price * quantity) AS item_total
    FROM order_items
    GROUP BY order_id
) oi ON o.order_id = oi.order_id
GROUP BY c.name;

Strategy 2: Use DISTINCT for Deduplication

When the duplication is exact (identical rows), DISTINCT removes it:

SELECT DISTINCT customer_id, order_id, amount
FROM orders;

But DISTINCT is a blunt instrument. It removes exact duplicates but does not handle cases where the same key has different values. Use it only when you are certain the duplicates are truly identical.

Strategy 3: Join on a Unique Key

If the table has a unique identifier at a finer grain, join on that instead:

-- Instead of joining on order_id (not unique in order_items)
-- Join on the composite key or the unique item_id
SELECT o.order_id, oi.item_id, oi.price
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id;

This does not reduce rows, but it makes the grain explicit. You now know the result is at the item level, not the order level.

Strategy 4: Validate with Known Totals

Before building a complex multi-join query, establish a baseline:

-- What is the true total revenue?
SELECT SUM(amount) FROM orders;

After building the full query, verify that the total still matches. If it does not, trace which join introduced the discrepancy by removing joins one at a time.

The Golden Rule

Never trust an aggregate from a multi-join query without verifying the row count. A query that returns a plausible-looking number can still be wrong. The row count check takes five seconds and catches the most common analytical SQL bug.