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