Skip to content
AI-Daily-Builder

2026-05-24 — 次浏览 · 4 models

LEFT JOIN 悄悄变成 INNER JOIN — SQL 正确性对决

Prompt

两张表：customers(id, name) 与 orders(id, customer_id, total)。同事写了这个查询，想列出每位客户及其订单数量，并且要「包含」零订单的客户：

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;

但结果中却少了零订单的客户。请明确解释原因，并改写查询，让零订单客户以 order_count = 0 出现。返回修正后的 SQL 以及一句话说明。

Notes

陷阱：对 LEFT JOIN 右表字段下的 WHERE 条件会在 join「之后」执行。未匹配（零订单）的客户其订单字段全为 NULL，而 NULL > 0 不为 TRUE，因此这些行被过滤掉 — 悄悄把 LEFT JOIN 变成 INNER JOIN。干净的修法是把条件移进 ON 子句（LEFT JOIN orders o ON o.customer_id = c.id AND o.total > 0），让它过滤 join 而非结果；此时 COUNT(o.id) 会忽略剩下的 NULL 并返回 0。判定标准：win = 正确诊断（WHERE 使外连接失效）且采用干净的 ON 子句修法；tie = 诊断正确但修法笨拙或部分正确（例如 WHERE ... OR o.id IS NULL，会错误处理「唯一订单 total <= 0」的客户）；loss = 诊断错误或修法仍丢失零订单行。延迟 / token / 成本为短单轮任务的代表值；本地模型行反映 DGX Spark 上的 Qwen3.6-35B-A3B NVFP4。

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.
请喝咖啡