相関副問合せとは
副問合せの中で「主問合せで参照している表の列」を参照する問合せ。非相関副問合せと異なり、主問合せの各行が処理されるたびに副問合せが繰り返し実行される点が最大の特徴。
非相関副問合せとの比較
| 比較項目 | 非相関副問合せ | 相関副問合せ |
|---|---|---|
| 実行タイミング | 1度だけ実行され、結果を主問合せに渡す | 主問合せの行ごとに繰り返し実行される |
| 主問合せへの依存 | 主問合せの列を参照しない | 主問合せの列を参照する |
| 単独実行 | ✅ 可能（独立して実行できる） | ❌ 不可（主問合せなしでは実行できない） |
| 主な使用用途 | 固定値・集計結果との比較 | 行ごとの相対比較、EXISTS条件 |
相関副問合せの実行の仕組み
- 主問合せが外側の表から1行取り出す
- その行の列値を使って副問合せを実行する
- 副問合せの結果を用いて、その行を出力するかどうか判断する
- 外側の表の全行に対して 1〜3 を繰り返す
-- 例：各社員の給与が自分の部門の平均給与より高い場合に取得する
SELECT e1.empno, e1.ename, e1.sal, e1.deptno
FROM emp e1
WHERE e1.sal > (SELECT AVG(e2.sal)
FROM emp e2
WHERE e2.deptno = e1.deptno); -- ← e1.deptno が主問合せの列
表別名の使用（重要）
-
相関副問合せで主問合せと同じ表を参照する場合（自己相関副問合せ）は、どちらの行セットを指しているか区別するために主問合せと副問合せそれぞれに異なる表別名を付けることが必須
-
主問合せと副問合せで異なる表を参照する場合は、列名が一意に特定できれば表別名を省略できることがあるが、可読性・保守性のため常に表別名を付けることを推奨
-- 自己相関副問合せ：同一表を使用するため表別名が必須
SELECT e1.empno, e1.ename
FROM emp e1 -- 主問合せ側の表別名
WHERE e1.sal > (SELECT AVG(e2.sal)
FROM emp e2 -- 副問合せ側の表別名（e1と区別するために必須）
WHERE e2.deptno = e1.deptno);
自己相関副問合せ
主問合せと副問合せで同じ表を参照する相関副問合せ。自己結合（Self Join）の考え方と同様に、同一表に異なる表別名を付けて区別する。
-- 例：自分の部門で最大給与を受け取っている社員のみ取得
SELECT e1.empno, e1.ename, e1.sal, e1.deptno
FROM emp e1
WHERE e1.sal = (SELECT MAX(e2.sal)
FROM emp e2
WHERE e2.deptno = e1.deptno);
相関副問合せによる UPDATE / DELETE
相関副問合せは SELECT 文だけでなく、UPDATE や DELETE などの DML 文でも使用できる。
UPDATE での使用
-- 例：各社員の給与を、同じ部門の平均給与に更新する
UPDATE emp e1
SET sal = (SELECT AVG(e2.sal)
FROM emp e2
WHERE e2.deptno = e1.deptno);
DELETE での使用
-- 例：自部門の平均給与を下回る社員を削除する
DELETE FROM emp e1
WHERE e1.sal < (SELECT AVG(e2.sal)
FROM emp e2
WHERE e2.deptno = e1.deptno);
EXISTS条件と NOT EXISTS条件
EXISTS とは
「副問合せが1行以上の行を返すかどうか」だけをテストする条件。EXISTS は TRUE / FALSE しか返さず、返された値の内容（列の値）は無視される。
-- 例：注文データが存在する顧客のみ取得
SELECT c.cust_id, c.cust_name
FROM customers c
WHERE EXISTS (SELECT 1
FROM orders o
WHERE o.cust_id = c.cust_id);
補足：
SELECT 1やSELECT *など副問合せのSELECTリストの中身は問わない。EXISTSは「行が返るかどうか」だけを見るためどちらでも動作は同じ。
NOT EXISTS とは
副問合せが1行も返さない場合に TRUEを返す。
-- 例：注文データが存在しない顧客のみ取得
SELECT c.cust_id, c.cust_name
FROM customers c
WHERE NOT EXISTS (SELECT 1
FROM orders o
WHERE o.cust_id = c.cust_id);
IN / NOT IN と EXISTS / NOT EXISTS の比較（試験頻出）
一見似ているが、NULL を含むデータに対する挙動が大きく異なるため試験で問われる。
| 比較項目 | IN / EXISTS | NOT IN / NOT EXISTS |
|---|---|---|
| 副問合せ結果にNULLが含まれる場合 |
IN：影響なし（NULL以外で一致を判定） |
NOT IN：行が1件も返らない（全条件が UNKNOWN になる） |
| NULLの扱い |
EXISTS：TRUEまたはFALSEのみ返す |
NOT EXISTS：TRUEまたはFALSEのみ返す（NULL影響なし） |
| 等価変換 |
IN ⇔ EXISTS は同値変換可能 |
NOT IN ⇔ NOT EXISTS は同値ではない（NULL有りの場合に結果が異なる） |
NOT IN に NULL が含まれると行が返らない理由
副問合せ結果に NULL が含まれると、(列 != NULL) の評価が UNKNOWN になる。NOT IN は内部的に AND で全条件を連結するため、UNKNOWN が混在すると条件全体が FALSE または UNKNOWN となり、最終的に1行も返らない。
-- ❌ 副問合せ結果に NULL が含まれると1行も返らない
SELECT ename FROM emp
WHERE empno NOT IN (SELECT mgr FROM emp); -- mgrにNULLが含まれる → 0件
-- (ORA-00000は出ないが意図しない0件)
-- ✅ NULL を除外してから NOT IN を使う
SELECT ename FROM emp
WHERE empno NOT IN (SELECT mgr FROM emp WHERE mgr IS NOT NULL);
-- ✅ NOT EXISTS を使う（NULL の影響を受けない）
SELECT e1.ename FROM emp e1
WHERE NOT EXISTS (SELECT 1
FROM emp e2
WHERE e2.mgr = e1.empno);
試験で意識したいポイントまとめ
| ポイント | 内容 |
|---|---|
| 相関副問合せの実行回数 | 主問合せの行数分だけ副問合せが繰り返し実行される |
| 非相関との違い | 非相関は単独実行可能、相関は主問合せに依存して単独実行不可 |
| 自己相関で表別名は必須 | 同一表を区別するために主問合せと副問合せに異なる表別名が必要 |
| UPDATE / DELETE でも使用可 | DML文でも相関副問合せを使った更新・削除が可能 |
| EXISTS の SELECT リスト | 何を書いても動作は同じ（SELECT 1 や SELECT * は等価） |
| IN と EXISTS は同値変換可能 |
NOT IN と NOT EXISTS はNULL有りの場合は同値ではない
|
| NOT IN + NULL → 0件 | 副問合せ結果に NULL が含まれると NOT IN は1行も返さない |
| NOT IN の対策 | 副問合せに WHERE 列 IS NOT NULL を追加するか、NOT EXISTS に書き換える |