SQL Scale Lab｜3,200万行で学ぶMySQL・SQL実践教材

前提: 第 5 章のあと npm run seed:basics で作り直してある。

この章で答える問い

SELECT の結果を、別の SELECT の中で使うには
IN と EXISTS はどう違うか
NOT IN で行が全部消えるのはなぜか

クエリ結果の再利用: 3通りの活用パターン

丸括弧 (...) で囲んだ SELECT 文は、そのクエリが返す結果の形式によって「単一の値（スカラ）」「値のリスト（集合）」「一時的なテーブル（表）」のいずれかとして扱えます。それぞれの活用パターンを見ていきましょう。

値として: 平均より高い明細

期待: 行数を見る。括弧の中が先に 1 つの数(平均 636.3)になる

SELECT id, product_id, unit_price
FROM order_items
WHERE unit_price > (SELECT AVG(unit_price) FROM order_items)
ORDER BY unit_price DESC

(SELECT AVG(unit_price) FROM order_items) は「全体の平均単価（636.3）」という 1 行 1 列の単一の数値を返します。そのため、通常の数値と同じように > などの比較演算子の右辺に直接配置できます。このようにクエリの中に埋め込まれた別のクエリをサブクエリ（副問合せ）と呼びます。

集合として: gold 会員の注文

期待: 22 行

SELECT id, customer_id, ordered_at
FROM orders
WHERE customer_id IN (SELECT id FROM customers WHERE tier = 'gold')

(SELECT id FROM customers WHERE tier = 'gold') は 1 列・複数行の「顧客 ID のリスト」を返します。そのため、複数の候補値と一致判定を行う IN 句の右辺に配置できます。第 4 章で学んだ JOIN を使っても同じ結果を取得できますが、「顧客名などの情報を取得したいなら JOIN」「注文の絞り込み条件として顧客条件を使うだけならサブクエリ」のように使い分けると、コードの意図が読み手に伝わりやすくなります。

表として: 顧客あたり平均注文数

期待: 1 行。平均 6.25、最大 12

SELECT AVG(t.件数) AS 平均, MAX(t.件数) AS 最大
FROM (
  SELECT customer_id, COUNT(*) AS 件数
  FROM orders
  GROUP BY customer_id
) AS t

FROM 句の中に配置したサブクエリは「仮想的なテーブル」として扱われます（この場合、テーブルの別名 AS t の付与が必須です）。「顧客ごとに注文件数を集計し、その集計結果の平均値や最大値をさらに計算したい」といった二段階の集約処理を行う際によく用いられ、派生テーブル（インラインビュー）と呼ばれます。

EXISTS: 条件に合致する行が 1 件でも存在するか判定する

キャンセルしたことのある顧客

期待: 2 行

SELECT name FROM customers c
WHERE EXISTS (
  SELECT 1 FROM orders o
  WHERE o.customer_id = c.id AND o.status = 'cancelled'
)

サブクエリの中に c.id という外側のクエリの列が登場しています。これは「外側の顧客 1 行ごとに、その顧客に紐付くキャンセル注文が 1 件でも存在するかどうかを調べる」という処理を行っています。このように外側のクエリの値を参照しながら実行されるサブクエリを相関サブクエリと呼びます。SELECT 1 は「条件に合致するレコードが 1 行でも存在するか（TRUE/FALSE）だけを判定するため、返す列の値は何でもよい」という慣用句です。

一度もキャンセルしていない顧客(チケット完了)

期待: 8 行

SELECT name FROM customers c
WHERE NOT EXISTS (
  SELECT 1 FROM orders o
  WHERE o.customer_id = c.id AND o.status = 'cancelled'
)

注意すべき挙動: NOT IN と NULL による全滅トラップ

「注文日時と発送日時が完全に一致する注文はないはず」という前提を検証するため、注文日時が「発送日時のリスト」に含まれない注文を数えてみましょう。全 50 件すべてが該当して 50 行が返ってくるように思えます。何行返ってくるか実行して確かめてみてください。

NOT IN と NULL

SELECT COUNT(*) FROM orders
WHERE ordered_at NOT IN (SELECT shipped_at FROM orders)

結果は 50 件ではなく、なんと 0 件になってしまいます。原因は、サブクエリが返した shipped_at のリストの中に、未発送注文の NULL が含まれていること です。x NOT IN (a, b, NULL) という条件は、内部的には x <> a AND x <> b AND x <> NULL と展開されます。第 2 章で学んだとおり、NULL との比較結果は真偽値ではなく「不明（UNKNOWN）」となります。 AND 条件の中に 1 つでも「不明」が混ざると式全体が「真」になれないため、すべての行が検索結果から脱落してしまうのです。

NOT IN はリストの各要素と <> で比較して AND で結合します。リスト内に NULL が 1 つでも含まれると式全体が「不明」となり、すべての行が除外されます。

NULL を除く

期待: 50

SELECT COUNT(*) FROM orders
WHERE ordered_at NOT IN (SELECT shipped_at FROM orders WHERE shipped_at IS NOT NULL)

NOT IN を使用する際は、サブクエリ側で必ず WHERE ... IS NOT NULL を指定して NULL を除外する必要があります。あるいは、先ほど学んだ NOT EXISTS を使用すれば、単に行の存在有無だけを判定するため NULL の影響を受ける心配がありません。実務では、NULL が混入しうる列に対する否定条件には NOT EXISTS を優先して採用するのがベストプラクティスです。

WITH 句（CTE）: サブクエリに名前を付けて可読性を高める

サブクエリや派生テーブルの入れ子が深くなると、クエリが非常に読みにくくなります。WITH 別名 AS (...) を使ってクエリの冒頭で一時的なビューを定義しておくと、上から下へ自然な流れでコードを追えるようになります。

平均より多く注文している顧客

期待: 上から順に読める

WITH per_customer AS (
  SELECT customer_id, COUNT(*) AS 件数
  FROM orders
  GROUP BY customer_id
)
SELECT c.name, p.件数
FROM per_customer p
JOIN customers c ON c.id = p.customer_id
WHERE p.件数 > (SELECT AVG(件数) FROM per_customer)
ORDER BY p.件数 DESC

ここでは定義した per_customer を JOIN と平均値計算の 2 箇所で再利用しています。通常の派生テーブルでは同じ集計クエリを 2 度書く必要がありますが、WITH 句（共通テーブル式: CTE）を使えば一度の定義で簡潔に記述できます。カンマで区切って複数の WITH 句を連鎖させることも可能です。

2 段の WITH: 月ごとの売上と、平均との差

期待: 3 行

WITH monthly AS (
  SELECT DATE_FORMAT(o.ordered_at, '%Y-%m') AS 月, SUM(oi.quantity * oi.unit_price) AS 売上
  FROM orders o JOIN order_items oi ON oi.order_id = o.id
  WHERE o.status <> 'cancelled'
  GROUP BY 月
),
with_avg AS (
  SELECT 月, 売上, 売上 - (SELECT AVG(売上) FROM monthly) AS 平均との差
  FROM monthly
)
SELECT * FROM with_avg ORDER BY 月

サブクエリは形式によって値・リスト・テーブルとして扱えます。NOT IN を使用する際は NULL による全滅に注意し、複雑な処理には WITH 句を活用しましょう。

確認

括弧で囲んだ SELECT が、値・集合・表のどれとして扱われるかは何で決まるか。

中の SELECT が返す結果の形で決まる。1 行 1 列なら値として比較の右に置け、1 列で複数行なら集合として IN の右に置け、FROM の中に書けば表(派生テーブル)になり、そのときは別名が要る。

NULL を許した列に対して、NOT IN より NOT EXISTS が好まれるのはなぜか。

NOT IN は集合の要素ごとの比較を AND でまとめるので、集合に NULL が 1 つでもあると全体が「不明」になり、その行が落ちる。NOT EXISTS は対応する行があるかないかしか見ないため、この罠がない。

同じ集計を 2 か所で使うとき、派生テーブルより WITH が読みやすいのはなぜか。

WITH は先に名前を付けて定義するので、同じ SELECT を 2 回書かずに済み、上から順に読める。派生テーブルは入れ子が深くなるほど読めなくなる。

次の章へ

次の「補足: ウィンドウ関数」は、行を集約せずにランキングや累計を計算する高度なテクニックを扱います（必要に応じてスキップしても第二部の学習に影響はありません）。第 7 章では、データの整合性を担保する最重要機能「トランザクション」を、2 つのデータベース接続を交互に操作しながら体験していきます。