LEFT OUTER JOIN と在庫数の合計
出典: 令和6年度 春期 応用情報技術者試験 午前 問26 (IPA)
“部品”表及び“在庫”表に対し，SQL 文を実行して結果を得た。SQL 文の a に入れる字句はどれか。
部品
| 部品 ID | 発注点 |
|---|---|
| P01 | 100 |
| P02 | 150 |
| P03 | 100 |
在庫
| 部品 ID | 倉庫 ID | 在庫数 |
|---|---|---|
| P01 | W01 | 90 |
| P01 | W02 | 90 |
| P02 | W01 | 150 |
〔結果〕
| 部品 ID | 発注要否 |
|---|---|
| P01 | 不要 |
| P02 | 不要 |
| P03 | 必要 |
〔SQL 文〕
SELECT 部品.部品 ID AS 部品 ID,
CASE WHEN 部品.発注点 > [ a ]
THEN N'必要' ELSE N'不要' END AS 発注要否
FROM 部品 LEFT OUTER JOIN 在庫
ON 部品.部品 ID = 在庫.部品 ID
GROUP BY 部品.部品 ID, 部品.発注点- ア COALESCE(MIN(在庫.在庫数), 0)
- イ COALESCE(MIN(在庫.在庫数), NULL)
- ウ COALESCE(SUM(在庫.在庫数), 0)
- エ COALESCE(SUM(在庫.在庫数), NULL)
正解と解説を見る
正解: ウ
- ア: 在庫数の最小値を使うと P01 は 90 となり、発注点 100 を下回って「必要」になってしまいます。結果は「不要」です。
- イ: 最小値を使う点が誤りで、NULL への置換も意味がありません。P01 が「必要」になってしまいます。
- ウ: 在庫数の合計を求め、在庫がない部品は 0 として扱うので、P01 は 180 で不要、P03 は 0 で必要になります。正しい答えです。
- エ: 合計は正しいのですが、NULL を NULL に置き換えても意味がなく、P03 の在庫数が NULL のままで比較が真にならず「不要」になってしまいます。
ポイント
- 部品ごとの在庫数は、倉庫別の行を 合計 (SUM) して求めます。P01 は 90 + 90 = 180 で、発注点 100 以上なので「不要」です。
- P03 は在庫表に行がなく、外部結合で在庫数が NULL になります。SUM の結果も NULL になるので、COALESCE(…, 0) で 0 に置き換えると、発注点 100 ＞ 0 で「必要」と判定できます。
- MIN だと P01 は 90 となり、180 ではなく発注点 100 を下回って「必要」になってしまい、結果と合いません。
- COALESCE(…, NULL) は NULL のままなので、P03 の比較が不明となり「不要」になります。