Skip to content
AI-Daily-Builder

2026-05-24 — ビュー · 4 models

LEFT JOIN がひそかに INNER JOIN になる — SQL 正確性の対決

Prompt

2 つのテーブル：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.
チップ