SQLで複雑なデータ抽出を行う際、「副問い合わせ（サブクエリ）」は避けて通れません。しかし、「単一行副問い合わせにより2つ以上の行が戻されました」というエラーに悩まされたり、複数の列をまとめて条件にしたい場面で詰まったりしていませんか？
この記事では、Oracle SQL における 非スカラー副問い合わせ の仕組みと、複数行・複数列を効率的に扱う実装テクニックを解説します。基本的な IN 演算子から、プロフェッショナルな「行値式（複数列比較）」までをマスターしましょう。
💰 【PR】Oracleエンジニアの市場価値、調べてみませんか？
Oracleのスキルは需要が高く、特定の資格や経験を持っていると年収が大幅にアップするケースがあります。まずはIT専門のエージェントで非公開求人をチェックしてみませんか？
結論・この記事でわかること（やることリスト）
非スカラー副問い合わせは、1行1列（スカラー）ではなく、「複数の行」や「複数の列（表）」を返す副問い合わせのことです。
- 複数行の比較:
IN演算子、またはANY/ALLを使用する。 - 複数列の比較:
(列A, 列B) IN (SELECT ...)の形式（行値式）を使用する。 - 表としての利用:
FROM句でインラインビューとして扱うか、WITH句を使用する。 - パフォーマンス: 大量データの場合は
EXISTSやJOINへの書き換えを検討する。
1. 非スカラー副問い合わせとは？
まず、用語と動作の違いを整理します。エラーの原因やクエリの意図を理解するために重要です。
| 種類 | 戻り値の形状 | 使用できる主な場所・演算子 | 特徴 |
| スカラー副問い合わせ | 1行 1列 | =, >, <, SELECT句など | 単一の値として扱える。複数行返るとエラーになる。 |
| 非スカラー副問い合わせ | 複数行 または 複数列 | IN, ANY, ALL, FROM句, EXISTS | 集合（リストや表）として扱われる。セット操作や結合に適している。 |
よくある利用シナリオ:
- 「特定の条件（例：勤務地がDALLAS）に合致する部署コードのリスト」に含まれる社員を探す（複数行）。
- 「部門ごとの最高給与」とその「部門番号」のペアに一致する社員を探す（複数列）。
2. テスト用データの作成（検証環境）
以下のSQLを実行して、検証用の emp（従業員）表と dept（部門）表を作成します。
※既存のテーブルがある場合は削除または別名で作成してください。
前提:
- スキーマに対する
CREATE TABLE,UNLIMITED TABLESPACE(またはクォータ) 権限が必要です。
-- 2.1 deptテーブルの作成
CREATE TABLE dept (
deptno NUMBER PRIMARY KEY,
dname VARCHAR2(50),
loc VARCHAR2(50)
);
-- 2.2 empテーブルの作成
CREATE TABLE emp (
empno NUMBER PRIMARY KEY,
ename VARCHAR2(50),
job VARCHAR2(50),
mgr NUMBER,
hiredate DATE,
sal NUMBER,
comm NUMBER,
deptno NUMBER,
CONSTRAINT fk_deptno FOREIGN KEY (deptno) REFERENCES dept(deptno)
);
-- 2.3 データの挿入
INSERT INTO dept VALUES (10, 'ACCOUNTING', 'NEW YORK');
INSERT INTO dept VALUES (20, 'RESEARCH', 'DALLAS');
INSERT INTO dept VALUES (30, 'SALES', 'CHICAGO');
INSERT INTO dept VALUES (40, 'OPERATIONS', 'BOSTON');
INSERT INTO emp VALUES (7369, 'SMITH', 'CLERK', 7902, TO_DATE('1980-12-17', 'YYYY-MM-DD'), 800, NULL, 20);
INSERT INTO emp VALUES (7499, 'ALLEN', 'SALESMAN', 7698, TO_DATE('1981-02-20', 'YYYY-MM-DD'), 1600, 300, 30);
INSERT INTO emp VALUES (7521, 'WARD', 'SALESMAN', 7698, TO_DATE('1981-02-22', 'YYYY-MM-DD'), 1250, 500, 30);
INSERT INTO emp VALUES (7566, 'JONES', 'MANAGER', 7839, TO_DATE('1981-04-02', 'YYYY-MM-DD'), 2975, NULL, 20);
INSERT INTO emp VALUES (7698, 'BLAKE', 'MANAGER', 7839, TO_DATE('1981-05-01', 'YYYY-MM-DD'), 2850, NULL, 30);
INSERT INTO emp VALUES (7782, 'CLARK', 'MANAGER', 7839, TO_DATE('1981-06-09', 'YYYY-MM-DD'), 2450, NULL, 10);
INSERT INTO emp VALUES (7788, 'SCOTT', 'ANALYST', 7566, TO_DATE('1987-07-13', 'YYYY-MM-DD'), 3000, NULL, 20);
INSERT INTO emp VALUES (7839, 'KING', 'PRESIDENT', NULL, TO_DATE('1981-11-17', 'YYYY-MM-DD'), 5000, NULL, 10);
INSERT INTO emp VALUES (7844, 'TURNER', 'SALESMAN', 7698, TO_DATE('1981-09-08', 'YYYY-MM-DD'), 1500, 0, 30);
INSERT INTO emp VALUES (7902, 'FORD', 'ANALYST', 7566, TO_DATE('1981-12-03', 'YYYY-MM-DD'), 3000, NULL, 20);
COMMIT;
SQL> SELECT * FROM dept;
DEPTNO DNAME LOC
---------- -------------------- --------------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL> SELECT * FROM emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- ---------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 13-JUL-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
10 rows selected.
3. 基本：複数行を返す副問い合わせ（IN演算子）
非スカラー副問い合わせの最も代表的なパターンです。副問い合わせが返す「値のリスト」の中に、対象カラムの値が含まれているかを判定します。
勤務地が ‘DALLAS’ の部署に所属する社員
= 演算子では副問い合わせが複数行（この例ではDALLASの部署が複数ある可能性がある場合など）を返すとエラーになりますが、IN なら安全に処理できます。
SELECT ename, job, deptno
FROM emp
WHERE deptno IN (
SELECT deptno
FROM dept
WHERE loc = 'DALLAS'
);
実行結果:
SQL> SELECT ename, job, deptno
2 FROM emp
3 WHERE deptno IN (
4 SELECT deptno
5 FROM dept
6 WHERE loc = 'DALLAS'
7 );
ENAME JOB DEPTNO
---------- ---------- ----------
SMITH CLERK 20
JONES MANAGER 20
SCOTT ANALYST 20
FORD ANALYST 20
ポイント:
INリストの中にNULLが含まれる場合、NOT INを使用すると結果が1件も返らない（UNKNOWNになる）現象が発生します。副問い合わせ側でWHERE col IS NOT NULLとするなどの対策が必要です。
4. 応用：複数列を返す副問い合わせ（行値式）
Oracle SQL の強力な機能の一つです。複数の列をペアとして比較します。「部門番号」と「その部門の条件」を同時に満たす行を探す際に非常に有効です。
部門ごとの「最大給与」をもらっている社員
部門ごとの最大給与を計算し、その「部門番号」と「給与額」の組み合わせに一致する社員を抽出します。
SELECT e.ename, e.sal, e.deptno
FROM emp e
WHERE (e.deptno, e.sal) IN (
-- ここで「部門」と「最大給与」の2列を返しています
SELECT deptno, MAX(sal)
FROM emp
GROUP BY deptno
);
解説:
- 副問い合わせで
GROUP BY deptnoし、部門ごとの最大給与を算出します（例: Dept 10 は 5000, Dept 20 は 3000…）。 - メインクエリの
WHERE (e.deptno, e.sal) IN ...で、自身の部門と給与がそのペアと完全一致するかを判定します。
実行結果:
SQL> SELECT e.ename, e.sal, e.deptno
2 FROM emp e
3 WHERE (e.deptno, e.sal) IN (
4 SELECT deptno, MAX(sal)
5 FROM emp
6 GROUP BY deptno
7 );
ENAME SAL DEPTNO
---------- ---------- ----------
BLAKE 2850 30
SCOTT 3000 20
KING 5000 10
FORD 3000 20
💰 【PR】Oracleエンジニアの市場価値、調べてみませんか？
Oracleのスキルは需要が高く、特定の資格や経験を持っていると年収が大幅にアップするケースがあります。まずはIT専門のエージェントで非公開求人をチェックしてみませんか？
5. インラインビュー：FROM句での副問い合わせ
副問い合わせの結果を「一時的なテーブル」と見なし、メインクエリで結合する方法です。これも非スカラー（表形式）の利用法です。
結合（JOIN）を使用する場合
IN ではなく JOIN を使うことで、副問い合わせ側の列（ここでは dname）を結果に表示できるようになります。
SELECT e.ename, e.job, d.dname
FROM emp e
JOIN (
-- 一時的なビューとして機能する副問い合わせ
SELECT deptno, dname
FROM dept
WHERE loc = 'DALLAS'
) d
ON e.deptno = d.deptno;
実行結果:
SQL> SELECT e.ename, e.job, d.dname
2 FROM emp e
3 JOIN (
4 SELECT deptno, dname
5 FROM dept
6 WHERE loc = 'DALLAS'
7 ) d
8 ON e.deptno = d.deptno;
ENAME JOB DNAME
---------- ---------- --------------------
SMITH CLERK RESEARCH
JONES MANAGER RESEARCH
SCOTT ANALYST RESEARCH
FORD ANALYST RESEARCH
WITH句（共通表式）を使用する場合
可読性を高めるために、副問い合わせを WITH 句として切り出すのが現代的なベストプラクティスです。
WITH dallas_depts AS (
SELECT deptno, dname
FROM dept
WHERE loc = 'DALLAS'
)
SELECT e.ename, e.job, d.dname
FROM emp e
JOIN dallas_depts d
ON e.deptno = d.deptno;
実行結果:
SQL> WITH dallas_depts AS (
2 SELECT deptno, dname
3 FROM dept
4 WHERE loc = 'DALLAS'
5 )
6 SELECT e.ename, e.job, d.dname
7 FROM emp e
8 JOIN dallas_depts d
9 ON e.deptno = d.deptno;
ENAME JOB DNAME
---------- ---------- --------------------
SMITH CLERK RESEARCH
JONES MANAGER RESEARCH
SCOTT ANALYST RESEARCH
FORD ANALYST RESEARCH
6. トラブルシューティング
非スカラー副問い合わせに関連する代表的なエラーと対処法です。
| ORAエラー | 原因 | 対処法 |
| ORA-01427 | 単一行副問い合わせにより2つ以上の行が戻されました | 副問い合わせが予期せず複数行を返しています。 1. = を IN に変更する。2. 副問い合わせに ROWNUM = 1 や MAX() 等を追加して1行に絞る。 |
| ORA-00913 | 値の個数が多すぎます | WHERE (A, B) IN (SELECT X, Y, Z ...) のように、比較する列数と副問い合わせの列数が一致していません。数を合わせます。 |
7. 運用・パフォーマンス上の注意
EXISTS との使い分け
大量のデータを扱う場合、IN よりも EXISTS（相関副問い合わせ）の方がパフォーマンスが良い場合があります。
EXISTS は条件に合致する行が1つ見つかった時点でスキャンを打ち切るためです。
EXISTSを使った書き換え例:
SELECT ename, job
FROM emp e
WHERE EXISTS (
SELECT 1
FROM dept d
WHERE d.deptno = e.deptno
AND d.loc = 'DALLAS'
);
実行結果:
SQL> SELECT ename, job
2 FROM emp e
3 WHERE EXISTS (
4 SELECT 1
5 FROM dept d
6 WHERE d.deptno = e.deptno
7 AND d.loc = 'DALLAS'
8 );
ENAME JOB
---------- ----------
SMITH CLERK
JONES MANAGER
SCOTT ANALYST
FORD ANALYST
インデックスの活用
副問い合わせの結合条件となる列（deptno など）や、フィルタ条件（loc）にインデックスを作成することで、FULL TABLE SCAN を回避し、高速化が期待できます。
-- 必要に応じて作成
-- CREATE INDEX idx_dept_loc ON dept(loc);
8. FAQ
Q1: 非スカラー副問い合わせで > や < は使えますか？
A1: そのままでは使えません。比較演算子の右側は単一の値である必要があるためです。ただし、> ALL（すべてより大きい）や > ANY（いずれかより大きい）という修飾子を付けることで可能になります。
Q2: 副問い合わせとJOIN、どちらを使うべきですか？
A2: 結果として「副問い合わせ側の列（例：部署名）」を表示したい場合は JOIN が必須です。フィルタリング（絞り込み）だけが目的であれば、可読性やオプティマイザの判断によりますが、IN や EXISTS の方が意図が伝わりやすい場合があります。
Q3: NULLが含まれる場合の挙動は？
A3: NOT IN を使用する際、副問い合わせの結果に一つでも NULL が含まれていると、結果は全て空になります。これはSQLの3値論理によるものです。NOT IN を使う際は、副問い合わせ側で WHERE col IS NOT NULL を必ず付けましょう。
9. まとめ
- 非スカラー副問い合わせ は、複数行・複数列のデータを柔軟に扱うための必須技術です。
- 単純なリスト比較には
IN、複合条件には(列A, 列B) INが便利です。 - 副問い合わせの結果を表として扱いたい場合は、
FROM句 またはWITH句 を活用しましょう。 - エラー
ORA-01427が出たら、副問い合わせが複数行を返していないか確認してください。
提供したデータセットを使って、実際に IN と EXISTS の実行計画の違いなどを確認してみることをお勧めします。
本記事は Oracle Database 19c を対象に解説します（他バージョンは画面や既定値が異なる場合があります）。
[参考]
Oracle Database SQL言語リファレンス 19c
💰 【PR】Oracleエンジニアの市場価値、調べてみませんか？
Oracleのスキルは需要が高く、特定の資格や経験を持っていると年収が大幅にアップするケースがあります。まずはIT専門のエージェントで非公開求人をチェックしてみませんか？
コメント