Your monthly revenue dashboard suddenly shows double or triple the expected numbers after someone joins an invoice table with a payments or line items table. You check the raw records, see no duplicates in the base table, and wonder why a simple aggregate function exploded.
textcustomer_id | order_id | calculated_total | actual_order_total
------------+----------+------------------+-------------------
1042 | 8841 | 450.00 | 150.00
1042 | 8842 | 120.00 | 120.00
1088 | 8901 | 1800.00 | 600.00
Why JOIN fan-out corrupts aggregations
Relational algebra does not care about your business logic. When you join table A (orders) to table B (line items) on a one-to-many relationship, the engine emits a new row for every matching pair. If an order has three line items, that single order row becomes three rows in the intermediate result set.
The pitfall hits when you join multiple one-to-many relationships simultaneously. Join orders to order_items (3 rows) and then join orders to order_payments (2 rows). The intermediate set is not 5 rows. It is the Cartesian product of those children for that parent: 6 rows.
When you run SUM(orders.shipping_fee) or SUM(order_payments.amount) across that join, the database sums the values across all six rows.
Here is the broken query pattern that causes the problem:
sqlSELECT
o.id AS order_id,
o.total_shipping,
SUM(i.price * i.quantity) AS calculated_items_total,
SUM(p.amount) AS total_paid
FROM orders o
LEFT JOIN order_items i ON o.id = i.order_id
LEFT JOIN order_payments p ON o.id = p.order_id
WHERE o.created_at >= '2024-01-01'
GROUP BY o.id, o.total_shipping;
If order 1 has 2 items and 2 payments, each item row joins with both payment rows. SUM(p.amount) doubles because each payment is duplicated across the matching items. total_shipping in the group by survives, but if you ran SUM(o.total_shipping) at the customer level, that shipping cost duplicates four times.
To fix fan-out, aggregate the child tables before joining them to the parent, or use independent common table expressions (CTEs).
sqlWITH aggregated_items AS (
SELECT
order_id,
SUM(price * quantity) AS calculated_items_total
FROM order_items
GROUP BY order_id
),
aggregated_payments AS (
SELECT
order_id,
SUM(amount) AS total_paid
FROM order_payments
GROUP BY order_id
)
SELECT
o.id AS order_id,
o.total_shipping,
COALESCE(ai.calculated_items_total, 0) AS calculated_items_total,
COALESCE(ap.total_paid, 0) AS total_paid
FROM orders o
LEFT JOIN aggregated_items ai ON o.id = ai.order_id
LEFT JOIN aggregated_payments ap ON o.id = ap.order_id
WHERE o.created_at >= '2024-01-01';
Every CTE produces exactly one row per order_id. The joins stay strictly one-to-one, eliminating row inflation before the final projection.
The LEFT JOIN filter trap: ON versus WHERE
Another silent bug happens when you try to filter a joined table. You write a LEFT JOIN because you want to keep all rows from the primary table even if there are no related records. Then you put a condition for the joined table in the WHERE clause.
Your LEFT JOIN just turned into an INNER JOIN.
sql-- BROKEN: Accidentally excludes users who have never placed an active order
SELECT
u.id,
u.email,
o.id AS order_id,
o.status
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE o.status = 'completed';
The database evaluates the FROM and JOIN clauses first. For users with no orders, the join fills o.id and o.status with NULL. Next, the database evaluates the WHERE clause: WHERE o.status = 'completed'. Because NULL = 'completed' evaluates to UNKNOWN (falsy in SQL boolean filtering), the engine discards every user without orders.
If your intent is to list all users alongside their completed orders while still displaying users who have zero completed orders, move the filter into the ON clause:
sql-- CORRECT: Keeps all users, only matches orders that are completed
SELECT
u.id,
u.email,
o.id AS order_id,
o.status
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
AND o.status = 'completed';
If you actually need to check a column from the right-hand table in the WHERE clause without breaking the LEFT JOIN, you must account for NULL:
sqlSELECT
u.id,
u.email,
o.id AS order_id,
o.status
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE (o.status = 'completed' OR o.id IS NULL);
Filter conditions on the right table placed in the ON clause decide what gets attached. Filter conditions placed in the WHERE clause decide what rows survive the final cut.
Window frame pitfalls with ORDER BY
Window functions often produce wrong running totals because SQL engines apply default frame specifications when you add an ORDER BY clause.
Consider this query:
sqlSELECT
account_id,
transaction_date,
amount,
SUM(amount) OVER (PARTITION BY account_id ORDER BY transaction_date) AS running_balance
FROM transactions;
Run this against an account with two transactions on the exact same date, and you get unexpected behavior:
textaccount_id | transaction_date | amount | running_balance
-----------+------------------+--------+----------------
401 | 2024-03-01 | 50.00 | 50.00
401 | 2024-03-02 | 100.00 | 350.00 <-- Jumped by 300 (includes both 100.00 and 200.00)!
401 | 2024-03-02 | 200.00 | 350.00 <-- Duplicate running total
401 | 2024-03-03 | 25.00 | 375.00
When you specify ORDER BY inside an OVER() clause without defining a frame, the SQL standard applies this default:
sqlRANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
RANGE treats peers, rows with identical values in the ORDER BY column, as a single unit. It evaluates the aggregate over all rows that share that value up to the current group edge. It does not calculate row by row.
To calculate an actual row-by-row cumulative sum, use ROWS instead of RANGE:
sqlSELECT
account_id,
transaction_date,
amount,
SUM(amount) OVER (
PARTITION BY account_id
ORDER BY transaction_date, id
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS running_balance
FROM transactions;
Adding ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW forces the engine to step through rows sequentially. Adding a secondary unique column like id to the ORDER BY clause makes the ordering deterministic. Without an explicit tie-breaker, the order of identical dates depends on engine-level storage retrieval, which can flip after an index rebuild or vacuum.
Edge case: Null values in window frame bounds
When working with sliding windows like rolling averages, developers frequently use offset-based frames:
sqlSELECT
machine_id,
recorded_at,
temperature,
AVG(temperature) OVER (
PARTITION BY machine_id
ORDER BY recorded_at
ROWS BETWEEN 5 PRECEDING AND CURRENT ROW
) AS rolling_avg
FROM sensor_readings;
If a sensor misses pings, ROWS BETWEEN 5 PRECEDING no longer represents a time period. It represents five arbitrary data points regardless of whether those readings occurred five minutes ago or five days ago.
If your database supports date and interval offsets in RANGE (PostgreSQL 11+, MySQL 8.0+, Oracle), use actual interval arithmetic instead of row counts:
sqlSELECT
machine_id,
recorded_at,
temperature,
AVG(temperature) OVER (
PARTITION BY machine_id
ORDER BY recorded_at
RANGE BETWEEN INTERVAL '1 hour' PRECEDING AND CURRENT ROW
) AS rolling_avg_last_hour
FROM sensor_readings;
Be careful with NULL columns inside window functions. If temperature contains NULL values, aggregate functions like AVG() ignore the nulls during calculation, but COUNT(*) counts the row while COUNT(temperature) skips the null.
sqlSELECT
machine_id,
recorded_at,
temperature,
COUNT(*) OVER w AS rows_in_window,
COUNT(temperature) OVER w AS valid_readings_in_window,
AVG(temperature) OVER w AS rolling_avg
FROM sensor_readings
WINDOW w AS (
PARTITION BY machine_id
ORDER BY recorded_at
ROWS BETWEEN 5 PRECEDING AND CURRENT ROW
);
Edge case: Outer joins with coalesced group keys
When aggregating child tables using GROUP BY, developers often write queries that group by columns from the nullable side of a LEFT JOIN.
sql-- DANGEROUS: Groups all unmatched stores into a single NULL bucket
SELECT
c.country_name,
s.store_name,
COUNT(o.id) AS total_orders
FROM countries c
LEFT JOIN stores s ON c.id = s.country_id
LEFT JOIN orders o ON s.id = o.store_id
GROUP BY c.country_name, s.store_name;
If a country has no stores, s.store_name is NULL. If multiple countries have no stores, grouping solely on s.store_name can collapse those distinct countries together depending on SQL dialect standards and selected non-aggregated columns.
Always group by the primary keys of the base tables to prevent unintended record squashing:
sqlSELECT
c.id AS country_id,
c.country_name,
s.id AS store_id,
s.store_name,
COUNT(o.id) AS total_orders
FROM countries c
LEFT JOIN stores s ON c.id = s.country_id
LEFT JOIN orders o ON s.id = o.store_id
GROUP BY c.id, c.country_name, s.id, s.store_name;
Verifying query plans for unintended Cartesian products
You can detect fan-out bugs during development before bad data hits reports. Inspect the execution plan using EXPLAIN or EXPLAIN ANALYZE.
Look for nested loops or hash joins where the output row estimate jumps by orders of magnitude compared to the input row estimates:
sqlEXPLAIN ANALYZE
SELECT
o.id,
SUM(i.price)
FROM orders o
LEFT JOIN order_items i ON o.id = i.order_id
LEFT JOIN order_payments p ON o.id = p.order_id
GROUP BY o.id;
In the output, search for the join step:
text-> Hash Join (cost=45.20..120.50 rows=15000 width=16) (actual time=0.12..3.45 rows=184200 loops=1)
Hash Cond: (p.order_id = o.id)
If actual rows in an intermediate join operation is significantly higher than the row count of your parent table, you have an unmitigated Cartesian product. Move the offending tables into pre-aggregated subqueries or CTEs immediately.