非スカラー副問い合わせについて

1. 非スカラー副問い合わせとは

非スカラー副問い合わせは、1つの列や値ではなく、複数の列や行を返す副問い合わせを指します。スカラー副問い合わせが1行1列の結果を返すのに対して、非スカラー副問い合わせは複数行または複数列を返すため、主にメインクエリでセット操作や結合が必要になります。

非スカラー副問い合わせは、データベース内の関連性を考慮し、柔軟で高度なデータ操作を可能にします。たとえば、複数条件を満たす行を動的に取得するシナリオで頻繁に使用されます。

---

2. テスト用のデータセット作成

以下のSQL文を使用して、記事内のクエリで使用するテーブルを作成し、データを挿入します。

2.1 empテーブルの作成

CREATE TABLE emp (
  empno NUMBER PRIMARY KEY,
  ename VARCHAR2(50),
  job VARCHAR2(50),
  mgr NUMBER,
  hiredate DATE,
  sal NUMBER,
  comm NUMBER,
  deptno NUMBER
);

2.2 deptテーブルの作成

CREATE TABLE dept (
  deptno NUMBER PRIMARY KEY,
  dname VARCHAR2(50),
  loc VARCHAR2(50)
);

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);

---

3. 使用例

3.1 IN演算子を使用する場合

非スカラー副問い合わせの最も基本的な使用方法は、IN演算子を使用する方法です。

SELECT ename, job
FROM emp
WHERE deptno IN (
  SELECT deptno
  FROM dept
  WHERE loc = 'DALLAS'
);

実行結果:

ename	job
SMITH	CLERK
JONES	MANAGER
SCOTT	ANALYST
FORD	ANALYST

3.2 EXISTS演算子を使用する場合

EXISTSを使用して非スカラー副問い合わせを記述することもできます。

SELECT ename, job
FROM emp e
WHERE EXISTS (
  SELECT 1
  FROM dept d
  WHERE e.deptno = d.deptno
  AND d.loc = 'DALLAS'
);

実行結果:

ename	job
SMITH	CLERK
JONES	MANAGER
SCOTT	ANALYST
FORD	ANALYST

---

4. 複数列を返す副問い合わせ

4.1 結合を使用する場合

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;

実行結果:

ename	job	dname
SMITH	CLERK	RESEARCH
JONES	MANAGER	RESEARCH
SCOTT	ANALYST	RESEARCH
FORD	ANALYST	RESEARCH

4.2 テーブル式を使用する場合

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;

実行結果:

ename	job	dname
SMITH	CLERK	RESEARCH
JONES	MANAGER	RESEARCH
SCOTT	ANALYST	RESEARCH
FORD	ANALYST	RESEARCH

---

5. 非スカラー副問い合わせの応用例

5.1 給与が平均を超える従業員のリスト

SELECT e1.ename, e1.sal
FROM emp e1
WHERE e1.sal > (
  SELECT AVG(e2.sal)
  FROM emp e2
);

実行結果:

ename	sal
JONES	2975
SCOTT	3000
FORD	3000
KING	5000

5.2 部門ごとの最大給与を持つ従業員の取得

SELECT e.ename, e.sal, e.deptno
FROM emp e
WHERE (e.deptno, e.sal) IN (
  SELECT deptno, MAX(sal)
  FROM emp
  GROUP BY deptno
);

実行結果:

ename	sal	deptno
CLARK	2450	10
JONES	2975	20
BLAKE	2850	30

---

6. パフォーマンス最適化のヒント

• インデックスの利用: 副問い合わせに使用する列にインデックスを作成することで、クエリの実行速度が向上します。CREATE INDEX idx_dept_loc ON dept(loc);
• 結合の適切な利用: 非スカラー副問い合わせの代わりに結合を使用することで、パフォーマンスが向上する場合があります。
• EXPLAIN PLANの活用: クエリの実行計画を確認し、ボトルネックを特定します。

---

7. まとめ

非スカラー副問い合わせは、複数行や複数列のデータを操作する際に強力なツールです。本記事では、基本的な使い方から応用例、パフォーマンス最適化までを解説しました。提供したテスト用データセットを使い、実際にクエリを実行してその効果を体感してください。

SQLのベストプラクティスを活用し、効率的で読みやすいクエリを構築しましょう。

[参考]
Oracle Database SQL言語リファレンス 19c

Oracle SELECT文の基本を解説

SELECT文はOracleデータベースを操作する上で最も基本的かつ重要なSQL文です。この文を使用することで、テーブルから必要な情報を抽出したり、条件を指定してデータを絞り込むことができます。本記事では、SELECT文の構文、基本例、応用...

oracle-master00000.com

2024.12.15