サブクエリ ① — スカラ・IN・WHERE 内の集計

本記事で使うデータ — employee テーブル

サブクエリ（subquery）とは、別のクエリの中に書かれた SELECT 文のことです。

「全社員の平均給与」を先に計算し、その結果を使って「平均より高い社員」を絞り込む、といった 段階的な問い合わせ を 1 文で書けます。

本記事では WHERE 句で使う 3 つの形 — スカラサブクエリ・IN サブクエリ・WHERE 内の集計（相関サブクエリ）— を順に押さえます。

演習に入る前に、employee テーブルの 列定義 と データのサンプル を確認しておきます。

① PRAGMA table_info(employee); で列名・型・主キーを確認してください。

② SELECT * FROM employee LIMIT 5; で先頭 5 行のデータをプレビューしてください。dept_id 列に NULL が入る社員がいる点も後の演習で扱います。

スカラサブクエリ — 1 行 1 列の結果を値として使う

スカラサブクエリ（scalar subquery: 結果が 1 行 1 列だけになるサブクエリ）は、ひとつの値 を返すので、= や > のような比較演算子の右辺にそのまま書けます。

(SELECT AVG(salary) FROM employee) は「全社員の平均給与」という 1 つの値を返すため、WHERE salary > (SELECT AVG(salary) FROM employee) と書けば「平均より高給な社員」を絞り込めます。

ポイントは 括弧で囲む ことと、1 行 1 列に必ず収まる こと（複数行が返るとエラーになります）。

AVG / MAX / MIN / COUNT のような集計関数は 1 つの値を返すので、スカラサブクエリでよく用いられます。

-- スカラサブクエリ: 最高給与と同額の社員を取り出す
SELECT name, salary
FROM employee
WHERE salary = (SELECT MAX(salary) FROM employee);

「全社員の平均給与より高い給料をもらっている社員の一覧を出したい」という要件を想定します。（正しく実行できれば解説が表示されます）

① employee テーブルから name と salary の 2 列を取り出してください。

② WHERE の条件で、salary が 全社員の平均給与より大きい 行に絞り込んでください。平均給与はスカラサブクエリで求めます。

③ salary の 降順 で並べてください。

IN サブクエリ — 別テーブルにある値で絞り込む

サブクエリが 複数行 1 列 を返す場合は、= ではなく IN を使います。

WHERE 列 IN (SELECT 別の列 FROM 別テーブル ...) と書くと、サブクエリが返した値の集合に含まれる行だけが残ります。

IN の右辺サブクエリは 1 列だけ を SELECT します（複数列を返すとエラーになります）。

否定したいときは NOT IN を使いますが、サブクエリ側に NULL が混ざると結果が変わる落とし穴があります（本記事の最後で扱います）。

-- IN サブクエリ: Tokyo にある部署に所属する社員
SELECT name, dept_id
FROM employee
WHERE dept_id IN (
  SELECT dept_id FROM department WHERE location = 'Tokyo'
);

「売上明細に 1 件以上登場した社員（実際に売上を計上した社員）の一覧を出したい」という要件を想定します。

① employee テーブルから emp_id と name の 2 列を取り出してください。

② sales テーブルに登場する社員 ID の集合をサブクエリで作り、emp_id がその集合に 含まれる 行に IN で絞り込んでください。

③ emp_id の 昇順 で並べてください。

WHERE 内の集計 — 相関サブクエリで部署平均と比べる

相関サブクエリの前に、サブクエリの 二重構造 を整理します。

社員一覧を取り出す メインの SELECT があり、その中に括弧で もう一つの SELECT（サブクエリ） を書きます。

ここまでのサブクエリは、メインの SELECT と無関係に 最初に 1 回だけ 計算される 非相関サブクエリ でした（例: 全社の平均給与は、どの社員を見るときも同じ 1 つの値です）。

これに対し、サブクエリが メインの SELECT が処理中の社員の列 を使うものを相関サブクエリ（correlated subquery: 社員ごとに計算し直すサブクエリ）と呼びます。

たとえば、「部署内トップ（自分の部署の最高給与と同額）の社員」を出す場合、部署ごとに最高給与は違うので、社員ごとに その部署だけ の最高給与が必要です。

以下の例ではメインの社員を emp、サブクエリ側を dept_member とし、WHERE dept_member.dept_id = emp.dept_id で相関させます。

社員が変わると渡る dept_id も変わり、結果が計算し直されます。

-- 相関サブクエリ: 自分の部署の最高給与と同額の社員（部署内トップ）
SELECT emp.name, emp.dept_id, emp.salary
FROM employee emp
WHERE emp.salary = (
    SELECT MAX(dept_member.salary)
    FROM employee dept_member
    WHERE dept_member.dept_id = emp.dept_id
  );

「各部署の中で、その部署の平均給与より高い給料をもらっている社員を出したい」という要件を想定します。

① employee テーブルに別名 emp を付け、name、dept_id、salary の 3 列を取り出してください。

② dept_id が NULL でない行に限定してください（所属部署のない社員は対象外）。

③ salary が その社員と同じ部署の平均給与 より大きい行に絞ってください。部署平均は、別名 dept_member を付けたサブクエリで dept_member.dept_id = emp.dept_id と相関させて求めます。

④ dept_id の昇順、同じ部署内では salary の降順で並べてください。

NOT IN と NULL の落とし穴

IN の否定は NOT IN ですが、NOT IN の右辺サブクエリに NULL が含まれると結果が大きく変わります。

なぜなら、NOT IN (..., NULL) となったとき、SQL は「値がどれとも等しくない」を 値 <> ... AND 値 <> NULL の形で確かめますが、値 <> NULL は真とも偽とも決まらない（NULL になる）ため、AND 全体が決して真にならず、どの行も残らないからです。

「誰の上司（マネージャー）でもない社員の名前を出したい」という要件を想定します。employee の manager_id 列には、上司がいない社員の NULL が混ざっています。（正しく実行できれば解説が表示されます）

① エディタの NOT IN をそのまま実行し、結果が 0 行 になってしまうことを確認してください。

② サブクエリ側で manager_id が NULL の行を除外し、誰の上司でもない社員が正しく出るように直してください。

③ emp_id の昇順で並べてください。