DoesItHold
Open the app
The DoesItHold field notes

Debug SQL JOINs by predicting cardinality first

A JOIN is easier to diagnose when you write down how many matches each left row is allowed to produce.

DoesItHold Editorial · · reviewed · guide-sql-joins-001

Direct answer

State the relationship, verify key uniqueness and make every required predicate explicit in the ON clause.

A JOIN returns one output row for each matching pair. If a customer key appears twice, one order can become two rows even when the SQL is syntactically valid. If a tenant or version predicate is missing, the multiplier can be much larger.

Before editing the query, describe the relationship in words: one customer per order, zero or one active profile per account, or many items per invoice. That expectation turns an unexplained row count into a bounded test.

Trace the failure
  1. 01
    Orders2 rows reference customer 7
  2. 02
    Customersid 7 exists in two tenants
  3. 03
    Loose JOIN2 × 2 matching pairs = 4 rows
  4. 04
    Full keytenant + id restores 2 rows
A JOIN emits matching pairs. When id is unique only inside a tenant, omitting tenant_id turns one intended match into several valid-looking pairs.

Minimal example

Scrollable code region

Buggy
SELECT o.id, c.name
FROM orders AS o
JOIN customers AS c
  ON c.id = o.customer_id;
Corrected
SELECT o.id, c.name
FROM orders AS o
JOIN customers AS c
  ON c.id = o.customer_id
 AND c.tenant_id = o.tenant_id;

What changes: The additional predicate expresses the real composite relationship; DISTINCT is unnecessary because the incorrect pairs are never produced.

Debugging method

Count each input, test key uniqueness, estimate matches per row, then compare that estimate with the joined count.

  1. Count rows in each input after applying the same filters as the final query.
  2. Group candidate keys and inspect any count greater than one.
  3. Sample a duplicated output key and trace every matching pair that produced it.
  4. Add the missing relationship predicate or enforce the uniqueness the model requires, then recount.

Common wrong fix

DISTINCT can erase visible duplicates while leaving an incorrect relationship and unnecessary work underneath. Grouping by every selected column has the same masking effect.

Limits and edge cases

A larger result is correct for one-to-many and many-to-many relationships. Outer joins also preserve unmatched rows and introduce null-extended columns. The goal is not to force the count downward; it is to make the observed cardinality agree with the declared relationship.

Sources