SELECTでサブクエリを使う方法

もう一度、SELECTのサブクエリについて話したいんだ。特に、内側のクエリが外側のクエリのデータを参照できるってことに注目してみよう。一見シンプルだけど、実はちょっとややこしい。もう一度このテーマを深掘りしてみよう…

SELECTのサブクエリを使うと、他のレコードやテーブルに依存する計算値やデータを持つ追加カラムを追加できるんだ。例えば、学生のリストを平均点付きで出したり、登録してるコース数やグループ内の今の最高点を表示したりできる。こういうのは、データを事前に加工しなくても「その場で」集計カラムを作って分析したい時に便利だよ。

SELECTのサブクエリの基本

例に入る前に、まずは基本の構文を確認しよう。SELECTのサブクエリはこんな感じ：

SELECT column1,
       column2,
       (SELECT 集計_または_条件 FROM 他の_テーブル WHERE 条件) AS 新しい_カラム名
FROM メイン_テーブル;

サブクエリは1つの値を返して、それが新しいカラムとして結果セットに現れるよ。このとき条件はメイン_テーブルのカラムを参照できる。

例1: 学生の平均点を追加する

まずはシンプルで役立つクエリから始めよう。studentsテーブルと、学生の成績が入ってるgradesテーブルがあるとする。

studentsテーブル：

gradesテーブル：

今度は、学生の名前と平均点を出したい。これにはSELECTのサブクエリを使うよ：

SELECT
    s.id,
    s.name,
    (SELECT AVG(g.grade) 
     FROM grades g 
     WHERE g.student_id = s.id) AS average_grade
FROM students s;

結果：

ここでのサブクエリ(SELECT AVG(g.grade) FROM grades g WHERE g.student_id = s.id)は、各学生の平均点を計算してる。studentsテーブルの各行ごとに1つの値を返してくれるから、JOINや事前のビューを作らなくてもOK。

例2: 各学生のコース数をカウントする

今度は、学生がいくつのコースを受講してるかも出してみよう。そのために追加のテーブルがある：

enrollmentsテーブル：

学生ごとに受講してるコース数を出すクエリ：

SELECT
    s.id,
    s.name,
    (SELECT COUNT(*)
     FROM enrollments e
     WHERE e.student_id = s.id) AS course_count -- 外側のstudentsテーブルを参照
FROM students s;

結果：

サブクエリ(SELECT COUNT(*) FROM enrollments e WHERE e.student_id = s.id)は、各学生のenrollmentsテーブル内のレコード数をカウントしてる。

サブクエリでのデータ集計

よくSELECTのサブクエリは集計データを計算するのに使う。AVG、SUM、COUNT、MAX、MINみたいな関数で、他のクエリの中で直接データを処理できるよ。

例3: 学生の合計点

今度は各学生の合計点も出してみよう。これはgradesテーブルの全成績の合計をサブクエリで計算する：

SELECT
    s.id,
    s.name,
    (SELECT SUM(g.grade)
     FROM grades g
     WHERE g.student_id = s.id) AS total_grade
FROM students s;

結果：

このサブクエリ(SELECT SUM(g.grade) FROM grades g WHERE g.student_id = s.id)は、各学生の成績を合計してる。もし成績がなければ、SUMはNULLを返すから注意してね。

制限とアドバイス

1. パフォーマンス。 SELECTのサブクエリはメインテーブルの各行ごとに個別に実行される。大量データだとかなり遅くなることも。できればJOINや、事前に集計したデータを使おう。例えば：

SELECT
    s.id,
    s.name,
    g.total_grade
FROM students s
LEFT JOIN (
    SELECT student_id, SUM(grade) AS total_grade
    FROM grades
    GROUP BY student_id
) g ON s.id = g.student_id;

このJOINのやり方だと、グループ化や集計が一度だけで済むから効率的だよ。

2. NULLの問題。

サブクエリでデータがなかった場合、結果はNULLになる。これが意外とハマりがち。例：

SELECT
    s.id,
    s.name,
    (SELECT SUM(g.grade)
     FROM grades g
     WHERE g.student_id = s.id) AS total_grade
FROM students s;

もしgradesにその学生のレコードがなければ、total_gradeはNULLになる。NULLを0にしたいならCOALESCE関数を使おう：

SELECT
    s.id,
    s.name,
    COALESCE((SELECT SUM(g.grade)
              FROM grades g
              WHERE g.student_id = s.id), 0) AS total_grade
FROM students s;

ここではCOALESCEの最初の引数に

(
    SELECT SUM(g.grade)
    FROM grades g
    WHERE g.student_id = s.id
)

SELECTのサブクエリ最適化

無駄な計算を減らしてパフォーマンスを上げるには：

1. サブクエリで使うカラムにはインデックスを貼ろう。例えばgradesテーブルのstudent_idにインデックスがあるとフィルタが速くなる。
2. できるならサブクエリをJOINで事前集計したデータに置き換えよう。
3. サブクエリで処理するデータ量はWHEREで絞ろう。

まとめの例：サブクエリを組み合わせる

これまでの知識を全部使って、学生の名前・平均点・コース数・合計点を出すクエリを作ってみよう：

SELECT
    s.id,
    s.name,
    (SELECT AVG(g.grade)
     FROM grades g
     WHERE g.student_id = s.id) AS average_grade,
    (SELECT COUNT(*) 
     FROM enrollments e 
     WHERE e.student_id = s.id) AS course_count,
    (SELECT SUM(g.grade)
     FROM grades g
     WHERE g.student_id = s.id) AS total_grade
FROM students s;

このクエリで、サブクエリの力を使って学生のフルプロフィールが一発で取れる。平均点・合計点・コース数も全部出せるし、VIEWやJOINを作らなくてもサクッと集計できるのがいいところ！