複雑なネストされたクエリの例：EXISTS、IN、HAVINGの組み合わせ

おめでとう、ついに本当に面白くなるところまで来たね！今日は、いろんな種類のサブクエリを組み合わせて、複雑な課題をどうやって解決するか見ていくよ。EXISTS、IN、HAVING ― このトリオを使いこなせば、まるでDBの魔法使いになった気分になれるはず。1つのテーブルからデータを取り出して、他のテーブルのデータでフィルタして、グループ化して、さらにグループをフィルタする。おまけに、クエリを効率よくするテクニックも紹介するよ。

まずは、レクチャーの中で少しずつ解いていく共通の課題を設定しよう。

課題の設定

例えば、大学のデータベースがあって、3つのテーブルがあるとしよう：

studentsテーブル

coursesテーブル

enrollmentsテーブル

次の条件を満たす全ての学生を選びたい：

1. 少なくとも1つのコースに登録している（EXISTS）。
2. 登録しているコースのうち、少なくとも1つで成績がない（IN）。
3. 平均点が80を超えるグループに所属している（HAVING）。

EXISTSとINを使った解決法

ステップ1：登録済みの学生をチェック（EXISTS）。 まずは一番シンプルな条件から。どの学生が少なくとも1つのコースに登録しているか知りたい。ここでEXISTSを使うよ。

SELECT name
FROM students s
WHERE EXISTS (
  SELECT 1
  FROM enrollments e
  WHERE e.student_id = s.id
);

1. 外側のクエリはstudentsテーブルから名前を選ぶ。
2. サブクエリでは、外側のクエリの特定の学生に対応するenrollmentsテーブルのレコードがあるかどうかをチェックしてる（WHERE e.student_id = s.id）。
3. SELECT 1は、内容じゃなくてレコードの存在だけが重要って意味。

結果：

これで、どの学生がコースに登録しているか分かった。でも、もっと絞り込みたい。成績がない学生だけをフィルタしたいんだ。

ステップ2：成績がないことをチェック（IN + NULL）。 ここでフィルタを追加しよう：少なくとも1つのコースで成績がない学生だけが欲しい。ここでINとNULLの扱いが役立つ。

SELECT name
FROM students s
WHERE id IN (
  SELECT e.student_id
  FROM enrollments e
  WHERE e.grade IS NULL
);

1. 外側のクエリで学生の名前を選ぶ。
2. サブクエリはenrollmentsテーブルからgrade IS NULLなstudent_idのリストを作る。

結果：

つまり、Ottoだけが成績がないコースを持っている唯一の学生。ドラマチックだね！でも、まだ終わりじゃない：平均点が80を超えるグループだけを考慮しないと。

HAVINGを使った解決法

ステップ3：HAVINGでグループ化とフィルタ。

ここで全部まとめる時が来た。やることは：

1. 各グループの平均点を計算する。
2. 平均点が80を超えるグループだけをフィルタする。
3. そのグループにいる学生を、前の条件も考慮して出す。

SELECT name
FROM students s
WHERE s.group_id IN (
  SELECT group_id
  FROM students
  JOIN enrollments ON students.id = enrollments.student_id
  WHERE grade IS NOT NULL
  GROUP BY group_id
  HAVING AVG(grade) > 80
)
AND id IN (
  SELECT e.student_id
  FROM enrollments e
  WHERE e.grade IS NULL
);

1. 外側のクエリは、全ての条件を満たす学生の名前を選ぶ。
2. WHEREの最初のサブクエリは、平均点が80を超えるグループのgroup_idリストを返す。
   • studentsとenrollmentsをJOINして成績を取得。
   • grade IS NOT NULLなレコードだけをフィルタ。
   • group_idでグループ化。
   • HAVINGでグループをフィルタ。
3. 2つ目のサブクエリは、その学生が少なくとも1つ成績がないコースを持っているかチェック。
4. 両方の条件はANDでつなげてる。

結果：

つまり、Ottoは成績がない唯一の学生であり、しかも成績優秀なグループに所属していることが分かった。

アプローチの比較：EXISTS vs IN

EXISTSは、レコードの存在を素早くチェックしたい時に最適。最初の1件を見つけた時点で検索を止めるから、大きなテーブルでは特に効率的だよ。

一方でINは、データの内容に注目したい時に便利。例えば、IDリストを出して後でフィルタしたい時とか。ただし、INのサブクエリが大量の値を返す場合は遅くなることもあるので注意。

HAVINGを使うタイミング

集計データで、結果に基づいてフィルタしたい時はHAVINGがベスト。ただ、もしWHEREで条件を移せるなら（例えばカラムでフィルタ）、クエリがシンプルになって実行も速くなるよ。

完全な例

もう1つ例をやってみよう：成績が75未満の学生が少なくとも1人いるけど、「哲学」コースには登録していないグループを選ぶ。

もう一度、テーブルをおさらい：

studentsテーブル

coursesテーブル

enrollmentsテーブル

SELECT DISTINCT group_id
FROM students s
WHERE group_id IN (
  SELECT s.group_id
  FROM students s
  JOIN enrollments e ON s.id = e.student_id
  WHERE e.grade < 75
)
AND group_id NOT IN (
  SELECT s.group_id                                 -- 1階層目のネストされたクエリ
  FROM students s
  JOIN enrollments e ON s.id = e.student_id
  WHERE e.course_id = (
    SELECT id FROM courses WHERE name = '哲学' -- 2階層目のネストされたクエリ :P
  )
);

1. 最初のサブクエリは、成績が75未満の学生がいるグループを選ぶ。
2. 2つ目のサブクエリは、「哲学」コースに関連するグループを除外する。
3. INとNOT INを組み合わせて、最終的な結果を得る。

結果：

これってどれくらい役立つ？

現実の世界では、こういうアプローチがデータの複雑な関係を分析する時にめっちゃ役立つ。例えば：

• 分析で「特別な」顧客グループ（VIPとか、問題児とか）を抽出したい時。
• レコメンドシステム開発で、ユーザーをいろんな条件でフィルタしたい時。
• 面接で、複雑なSQLクエリの最適化を求められた時。

練習あるのみ！これが君のマスターへの道だよ。