Debug SQL joins by cardinality, keys, and nullability

An INNER JOIN emits only matching row pairs. A LEFT JOIN also preserves every left-side row, filling right-side columns with NULL when no match exists. That definition is simple; production failures arise when “match,” row multiplicity, nullability, and predicate placement were never made explicit.

Write the expected grain before touching the query

Suppose the result should contain one row per customer, but the query joins customers to orders. A customer can have many orders, so the raw join’s grain is closer to one row per matching customer-order pair. No join keyword can turn that one-to-many relationship into one row per customer. You must aggregate, select one related row, or change the requirement.

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

Before running it, record the intended left and right keys, whether each is unique, whether either can be null, and the maximum expected matches per row. Then measure instead of trusting a schema diagram:

SELECT customer_id, COUNT(*)
FROM customers
GROUP BY customer_id
HAVING COUNT(*) > 1;

SELECT customer_id, COUNT(*)
FROM orders
GROUP BY customer_id
ORDER BY COUNT(*) DESC;

Duplicate keys on both sides create a many-to-many multiplication. Three left rows matching four right rows produce twelve result rows. Adding DISTINCT can hide the symptom while preserving wrong totals, unstable choices, and wasted work.

Choose preservation semantics deliberately

• INNER JOIN: one output row for each pair satisfying the condition; unmatched rows on either side disappear.
• LEFT OUTER JOIN: the inner result plus one null-extended row for every unmatched left row.
• RIGHT OUTER JOIN: the converse; many teams rewrite it as a left join to keep a consistent reading direction.
• FULL OUTER JOIN: matches plus unmatched rows from both inputs, null-extending the opposite side.
• CROSS JOIN: every possible pair, producing N × M rows before later filtering.

OUTER is optional syntax; LEFT JOIN and LEFT OUTER JOIN mean the same thing. “Intersection” and “union” diagrams are only a loose teaching aid. SQL joins combine rows, retain columns from both inputs, honor duplicates, and evaluate three-valued logic. Set diagrams often conceal exactly the cases that break production queries.

The ON-versus-WHERE trap

A predicate in ON controls which right rows match. A predicate in WHERE filters the joined result. With an outer join, that difference can remove the very null-extended rows you meant to preserve.

-- Preserve every customer, attaching only paid orders.
SELECT c.customer_id, o.order_id
FROM customers AS c
LEFT JOIN orders AS o
  ON o.customer_id = c.customer_id
 AND o.status = 'paid';

-- This rejects rows where o.status is NULL,
-- effectively requiring a matching paid order.
SELECT c.customer_id, o.order_id
FROM customers AS c
LEFT JOIN orders AS o
  ON o.customer_id = c.customer_id
WHERE o.status = 'paid';

Sometimes the second query is correct; the failure is treating the two forms as interchangeable. State which population must survive and place predicates accordingly.

NULL does not match NULL with ordinary equality

If either join key is null, a.key = b.key evaluates to unknown, not true. Do not coalesce missing keys to a magic value unless that value is impossible and the business semantics genuinely equate “missing” on both sides. PostgreSQL provides IS NOT DISTINCT FROM for null-safe equality, but using it changes the relationship and can create large null-to-null matches.

SELECT COUNT(*) FILTER (WHERE customer_id IS NULL) AS null_keys
FROM orders;

Also distinguish an unmatched right row from a matched row whose nullable column happens to be null. Test a non-nullable right-side key when classifying matches:

CASE WHEN o.order_id IS NULL
     THEN 'no matching order'
     ELSE 'matched'
END

Diagnose a surprising result in controlled stages

1. Freeze the query text, parameters, transaction isolation, and a representative dataset.
2. Count each input after its own filters.
3. Validate supposed keys and foreign-key coverage.
4. Join only key columns and count total rows, distinct left keys, and distinct right keys.
5. List unmatched keys with an anti-join.
6. Add predicates and additional joins one at a time, recording the count change.
7. Inspect the execution plan only after correctness is understood.

-- Left rows without a match
SELECT c.customer_id
FROM customers AS c
WHERE NOT EXISTS (
  SELECT 1
  FROM orders AS o
  WHERE o.customer_id = c.customer_id
);

NOT EXISTS expresses an anti-join without the null pitfalls of NOT IN. For reconciliation, run the complementary check from orders to customers and investigate orphaned foreign keys.

Make one-row requirements explicit

When the requirement is the latest order per customer, preselect it deterministically rather than joining all orders and deduplicating afterward. In PostgreSQL, a lateral subquery is one clear option:

SELECT c.customer_id, latest.order_id
FROM customers AS c
LEFT JOIN LATERAL (
  SELECT o.order_id
  FROM orders AS o
  WHERE o.customer_id = c.customer_id
  ORDER BY o.created_at DESC, o.order_id DESC
  LIMIT 1
) AS latest ON true;

The secondary order key makes ties deterministic. A window function or grouped aggregate may suit other databases and workloads. Confirm that the chosen row matches the business definition—not merely the fastest query.

Verification, performance, and rollout

Create fixtures covering zero, one, and many matches; duplicate keys; null keys; deleted parents; identical timestamps; and predicates that match no right rows. Assert both row count and values. Reconcile totals against a trusted report before changing production consumers.

Indexes on join keys can reduce work, but they do not correct semantics. Check actual plans and row estimates on production-shaped data. Stale statistics, implicit casts, functions on indexed columns, and skew can turn a correct join into an expensive hash, sort, or nested-loop operation. A full outer join may be excellent for reconciliation and too expensive for an online request path.

Roll out changed reporting queries beside the old version. Compare keyed outputs, not only grand totals. Keep the prior query and a reversible feature flag until consumers agree on the new grain. If counts surge, stop downstream writes and exports before rollback; duplicated financial or notification side effects may need business-level compensation.

Attribution and verification

This guide was prompted by Chris de Vries’s Stack Overflow question and Mark Harrison’s accepted answer, used under CC BY-SA. The accepted examples correctly introduce join preservation, but this guide corrects the common Venn-diagram oversimplification by accounting for duplicate rows, nulls, and predicate placement. Current semantics were independently verified against PostgreSQL’s primary joined-table documentation and its comparison and NULL predicate documentation.