Loading page
About this tool
Find out why your SUM is too high after a join: describe each table's grain and key cardinality to get the row-multiplication factor, which aggregates are inflated, and the corrected SQL.
The SQL JOIN Fan-Out Debugger computes the row-multiplication factor of a join and tells you exactly which aggregates it corrupts: describe each table's grain and its join key cardinality, and it returns the fan-out factor for every table, marks each SUM, COUNT, AVG, MIN and MAX as inflated, distorted or safe, and emits the corrected SQL. The rule it applies is the definition of a joined table in ISO/IEC 9075-2 §7.10 — a join is a filtered cross product, so one parent row matching m child rows becomes m result rows, and two fanning children of the same parent multiply to m1 x m2. It is for analysts and engineers whose total went up after adding a join and who need to know the exact factor before trusting the number.
Open SQL JOIN Fan-Out Debugger on AltFTool — it loads instantly in your browser.
Under 1. Describe your join, set Rows in the base table and Base grain (one row per what?), then fill each fieldset's Table name, Alias, Join key column and Primary key (used by the window fix). Add a joined table adds tables up to a limit of six, each with its own Joins to parent, Join key cardinality (1:1, 1:many or many:many), Average matching rows per parent row, Join type (INNER JOIN or LEFT JOIN), Parent rows with no match (%) and Of those, NULL join keys (%).
Under 2. Which columns do you aggregate?, use Add an aggregate — up to ten — and give each one a Function (SUM(col), COUNT(*), COUNT(col), COUNT(DISTINCT col), AVG(col), MIN(col), MAX(col) or SUM(DISTINCT col)), a From table and a Column. There is no run button: every field recomputes the answer as you type.
Fan-out result prints the multiplication factor above Base rows in, Rows out of the join, Extra rows created and Base rows dropped by inner joins, then grades every aggregate Survives, Inflated, Distorted or Not a fix, and Copy takes that whole summary. Under Your query, as written and corrected, switch between As written, Fix 1 — pre-aggregate, Fix 2 — window de-dup and, when a LEFT JOIN is filtered in WHERE, Fix 3 — move WHERE to ON, then Copy the SQL; Reset returns every field to the starting example.
Each table gets its own duplication factor: total multiplicity divided by the multiplicity on the path from the base table, so a child that fans 3x can still leave its own SUM only 2x too high.
SUM and COUNT scale with the fan-out, MIN, MAX and COUNT(DISTINCT) survive it exactly, AVG survives only uniform fan-out, and SUM(DISTINCT) is never a fix because it de-duplicates values rather than rows.
Both the pre-aggregated subquery form and the ROW_NUMBER window form are emitted against your own table names, aliases and keys, with the aggregates that cannot compose through a pre-aggregate marked in the SQL.
Three small tables you can change in place: 3 orders totalling 1,300 report 3,900 through the naive join, and both fixes bring it back to 1,300.
Because the join duplicated the rows the SUM reads. A join is a filtered cross product (ISO/IEC 9075-2 §7.10), so an order that has 3 line items appears on 3 result rows and its order_total is added 3 times. The factor is exact: if the base table's rows are each repeated d times, SUM is d times the true value. Join a second 1:many table with 2 matches per order and the factor becomes 3 x 2 = 6.
No. SUM(DISTINCT col) removes duplicate values, not duplicate rows — two different orders that both total 500 collapse into a single 500, so a true total of 1,300 comes back as 800. COUNT(DISTINCT id) does survive fan-out, because DISTINCT reduces the duplicated rows to a set of ids before counting. The two working fixes are pre-aggregating each fanning table to one row per join key, or keeping the wide rowset and using ROW_NUMBER() OVER (PARTITION BY the table's own primary key) with SUM(CASE WHEN rn = 1 THEN col END).
Because a WHERE predicate on the right table turns it back into an INNER JOIN. WHERE is evaluated after the from clause (ISO/IEC 9075-2 §7.4), so the null-extended rows the LEFT JOIN just created make the predicate UNKNOWN, and only rows evaluating to True are kept. Moving the condition into the ON clause — or writing it as (p.status = 'captured' OR p.order_id IS NULL) — keeps the join outer.
Because NULL = NULL is Unknown, not true (ISO/IEC 9075-2 §8.2), and a join keeps only rows whose condition evaluates to True. A row whose key is NULL therefore matches nothing at all, not even another NULL key, and an INNER JOIN discards it silently. Counting rows WHERE key IS NULL on both sides before joining shows how many are at stake.