SQL：サブクエリ

2行。ノートPC / モニター

WHERE price > (SELECT AVG(price) FROM products) と書く。サブクエリは丸カッコで囲む。

SELECT name, price
FROM products
WHERE price > (SELECT AVG(price) FROM products);

先に平均を計算し、その値で絞り込んでいる。平均値は35200円だが、この数字を自分で調べて書き込む必要はない——サブクエリが自動で計算してくれる。

解答 == B

関数の引数と同じ記号。

B

丸カッコで囲まないと構文エラーになる。内側のSELECTが先に実行され、その結果が外側から見える。

5行。ノートPC / マウス / キーボード / モニター / USBメモリ

WHERE id IN (SELECT product_id FROM orders) と書く。

SELECT name
FROM products
WHERE id IN (SELECT product_id FROM orders);

orders テーブルに登場する product_id のリストと照合している。新商品Aとサンプル品は一度も注文されていないので除外される。

SELECT name
FROM products
WHERE price = (SELECT ____(price) FROM products);

1行。ノートPC

「最大」を意味する3文字の集計関数。

MAX

「最高値と等しい商品」を探すという書き方。ORDER BY と LIMIT 1 でも同じことができるが、サブクエリなら同点があっても全部拾える。

解答 == B

比較演算子は「値1つ」を前提にしている。

B

環境によって挙動が違う危険な書き方。多くの環境ではエラーになるが、一部では警告なく最初の1行だけが使われることがある。複数行を扱うなら IN を使う。

2行。新商品A / サンプル品

WHERE id NOT IN (SELECT product_id FROM orders) と書く。orders.product_id にNULLは無いので安全に使える。

SELECT name
FROM products
WHERE id NOT IN (SELECT product_id FROM orders);

今回は product_id にNULLが無いので正しく動く。ただし実務のデータではNULLが混ざることがあり、その場合は次の問題のように対策が必要になる。

SELECT name FROM products
WHERE id NOT IN (
  SELECT product_id FROM orders WHERE product_id ____ NULL
);

2行。新商品A / サンプル品

NULLを除外する条件（SQL05・SQL10）。

IS NOT

サブクエリの中でNULLを先に除いておくのが安全策。この一手間を忘れると、customer_id や product_id にNULLが1件でも入った瞬間、NOT IN の結果が全体で0件になる。

解答 == B

NULLとの比較は「不明」を返す。「不明」を含むANDやORの結果は。

B

これがNOT INの罠の核心。「1でも2でもNULLでもない」という判定に、NULLとの比較が紛れ込むと結果全体が確定できなくなり、SQLは安全側に倒して0件を返す。

3行。田中商事 / 鈴木工業 / 佐藤物産

WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id) と書く。外側のテーブルに別名を付けておく。

SELECT name
FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

EXISTS は中身の値を見ず、行があるかないかだけを判定する。SELECT 1 の1に意味はなく、何を選んでも結果は変わらない。

2列7行。ノートPC 2 / 新商品A 0

SELECT の中に (SELECT COUNT(*) FROM orders o WHERE o.product_id = p.id) と書く。

SELECT
  p.name,
  (SELECT COUNT(*) FROM orders o WHERE o.product_id = p.id) AS 注文数
FROM products p;

SELECT の中にもサブクエリを書ける。外側の p.id を内側のサブクエリが参照しており、商品ごとに違う結果を返す（相関サブクエリ）。

3列3行。ノートPC 128000 92800.0 が先頭

WHERE と SELECT の両方でスカラサブクエリを使う。同じサブクエリを2回書くことになる。

SELECT
  name,
  price,
  price - (SELECT AVG(price) FROM products) AS 差額
FROM products
WHERE price >= (SELECT AVG(price) FROM products)
ORDER BY 差額 DESC;

同じサブクエリをWHEREとSELECTの両方で使っている。書く手間は増えるが、「平均」という基準を数値としてハードコードしていないので、データが増減しても常に正しい結果になる。

3列2行。新商品A / サンプル品

サブクエリの中に WHERE product_id IS NOT NULL を必ず入れる。

SELECT name, category, stock
FROM products
WHERE id NOT IN (
  SELECT product_id FROM orders WHERE product_id IS NOT NULL
);

SQL10のLEFT JOINと同じ結果を、サブクエリでも作れる。書き方は違っても目的が同じなら結果は一致する——これがSQLの面白いところで、複数の道具で同じ課題を解けるようになる。

2列1行。高橋建設 福岡

NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id) と書く。

SELECT name, area
FROM customers c
WHERE NOT EXISTS (
  SELECT 1 FROM orders o WHERE o.customer_id = c.id
);

NOT EXISTS は customer_id にNULLが混ざっていても安全。存在の有無だけを見ているので、NOT INのような罠が起きない。「無いことを調べる」なら NOT EXISTS が第一候補。

3列3行。田中商事 ノートPC 256000 / 鈴木工業 モニター 45000 / 佐藤物産 ノートPC 128000

WHERE の中で、同じ顧客の注文だけを対象にした MAX を計算する。サブクエリの中で外側の customer_id を参照する。

SELECT
  c.name AS 顧客,
  p.name AS 商品,
  p.price * o.qty AS 金額
FROM orders o
JOIN customers c ON o.customer_id = c.id
JOIN products  p ON o.product_id  = p.id
WHERE p.price * o.qty = (
  SELECT MAX(p2.price * o2.qty)
  FROM orders o2
  JOIN products p2 ON o2.product_id = p2.id
  WHERE o2.customer_id = o.customer_id
);

「顧客ごとの最大」という基準が、顧客によって変わるのが相関サブクエリの本質。田中商事の基準は256000円、鈴木工業の基準は45000円——サブクエリが行ごとに違う値で評価される。

2行。ノートPC / キーボード

サブクエリの中で WHERE category = p.category と、外側のカテゴリを参照する。

SELECT name, category, price
FROM products p
WHERE price > (
  SELECT AVG(price)
  FROM products p2
  WHERE p2.category = p.category
);

全体の平均ではなく「自分のカテゴリ内の平均」と比較している。PCの中ではノートPCが平均より高く、周辺機器の中ではキーボードが平均より高い——カテゴリごとに基準が変わる高度な絞り込み。