2026-05-24 — views · 4 models

LEFT JOIN silently becomes INNER JOIN — SQL correctness head-to-head

Prompt

Two tables: customers(id, name) and orders(id, customer_id, total). A teammate wrote this query to list every customer with their number of orders, INCLUDING customers who have placed zero orders:

SELECT c.id, c.name, COUNT(o.id) AS order_count
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.total > 0
GROUP BY c.id, c.name;

But customers with zero orders are missing from the result. Explain exactly why, and rewrite the query so zero-order customers appear with order_count = 0. Return the corrected SQL and a one-sentence explanation.

Notes

The trap: a WHERE predicate on a column from the right table of a LEFT JOIN runs AFTER the join. Unmatched (zero-order) customers get all-NULL order columns, and `NULL > 0` is not TRUE, so those rows are filtered out — silently collapsing the LEFT JOIN into an INNER JOIN. The clean fix is to move the predicate into the ON clause (`LEFT JOIN orders o ON o.customer_id = c.id AND o.total > 0`), so it filters the join rather than the result; COUNT(o.id) then ignores the remaining NULL and returns 0. Verdict criteria: win = correct diagnosis (WHERE nullifies the outer join) AND clean ON-clause fix; tie = correct diagnosis but a clunky/partially-correct fix (e.g. `WHERE ... OR o.id IS NULL`, which mishandles customers whose only orders have total <= 0); loss = wrong diagnosis or a fix that still drops zero-order rows. Latency / token / cost figures are representative for a short single-turn task; the local model row reflects a Qwen3.6-35B-A3B NVFP4 on a DGX Spark.

Results — 4 models

claude-opus-4-7 WIN · 4180ms · in 190 · out 182 · $0.016

The WHERE o.total > 0 runs AFTER the LEFT JOIN. For a customer with zero orders every o.* column is NULL, and NULL > 0 is not TRUE, so those rows are dropped — collapsing the LEFT JOIN into an INNER JOIN. Move the predicate into the ON clause so it filters the join, not the result:

SELECT c.id, c.name, COUNT(o.id) AS order_count
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id AND o.total > 0
GROUP BY c.id, c.name;

Unmatched customers keep one all-NULL row and COUNT(o.id) correctly returns 0.

gpt-5 WIN · 3360ms · in 190 · out 168 · $0.007

A post-join WHERE on a right-table column nullifies the outer join: zero-order customers produce NULL o.total, and NULL > 0 fails, so they are filtered out. Relocate the filter to the join condition:

SELECT c.id, c.name, COUNT(o.id) AS order_count
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id AND o.total > 0
GROUP BY c.id, c.name;

COUNT(o.id) skips NULLs, so zero-order customers report 0.

gemini-3-pro TIE · 2980ms · in 190 · out 176 · $0.002

Right — the WHERE clause removes the NULL rows produced by the LEFT JOIN, so customers with no orders disappear. Keep them by also allowing the NULL case:

SELECT c.id, c.name, COUNT(o.id) AS order_count
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.total > 0 OR o.id IS NULL
GROUP BY c.id, c.name;

This restores the zero-order customers.

qwen3.6-35b-a3b-nvfp4 (local DGX Spark) LOSS · 9100ms · in 190 · out 151

The problem is COUNT with GROUP BY: COUNT(o.id) returns NULL when there are no matching orders, so wrap it in COALESCE to show 0:

SELECT c.id, c.name, COALESCE(COUNT(o.id), 0) AS order_count
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.total > 0
GROUP BY c.id, c.name;

This defaults missing counts to 0.