サブクエリとは、上位のクエリにネストされたクエリを指します。SQL言語は多層ネストされたクエリを許可しています。つまり1つのサブクエリの中に他のサブクエリをネストすることができます。
サブクエリの紹介
サブクエリは、SQLステートメントのさまざまな句に記述できます。例えば、SELECT 句、FROM 句、WHERE 句などです。SELECT ステートメントの FROM 句におけるサブクエリは、インラインビューとも呼ばれます。インラインビューに任意の数のサブクエリをネストできます。SELECT ステートメントの WHERE 句におけるサブクエリは、ネストされたサブクエリとも呼ばれます。
サブクエリの種類
結果セットの行と列の数に応じて、以下の4種類のサブクエリに分けることができます：
| サブクエリの種類 | 結果セット | 関連する句 |
|---|---|---|
| スカラーサブクエリ | 単一列・単一行 |
|
| 列サブクエリ | 単一列・複数行 |
|
| 行サブクエリ | 複数列・複数行 |
|
| テーブルサブクエリ | 複数行・複数列 |
|
サブクエリの適用シナリオ
サブクエリは主に以下のシーンで使用されます：
INSERTまたはCREATE TABLEステートメント内で挿入先のターゲットテーブルに含まれる行セットを定義します。CREATE VIEWステートメント内で含める行セットを定義します。UPDATEステートメント内で、既存の行に割り当てる1つまたは複数の値を定義します。SELECT、UPDATE、およびDELETEステートメントのWHERE句、HAVING句、またはSTART WITH句の条件に値を提供します。
サブクエリのキーワード
サブクエリのキーワードには IN、ANY、SOME、ALL などが含まれます。
INはWHERE式でよく使用され、特定の範囲内のデータを照会するために使われます。ANYとSOMEは=、>、>=、<、<=、<>演算子と組み合わせて使用でき、それぞれ等しい、より大きい、以上、より小さい、以下、または等しくない任意のデータを示します。ALLは=、>、>=、<、<=、<>演算子と組み合わせて使用でき、それぞれ等しい、より大きい、以上、より小さい、以下、または等しくないすべてのデータを示します。注意
NOT INを使用していて、サブクエリ内の列の値がNULLである場合、外部クエリの結果は空になります。
サブクエリの例
サンプルテーブル emp と dept を作成し、適切なデータを挿入します。
CREATE TABLE dept(
deptno NUMBER(2,0),
dname VARCHAR(14),
location VARCHAR(13),
CONSTRAINT pk_dept PRIMARY KEY(deptno)
);
INSERT INTO dept VALUES (20,'ACCOUNTING','Los Angeles');
INSERT INTO dept VALUES (30,'OPERATIONS','CHICAGO');
INSERT INTO dept VALUES (40,'SALES','NEW YORK');
CREATE TABLE emp(
empno NUMBER(4,0),
empname VARCHAR(10),
job VARCHAR(9),
mgr NUMBER(4,0),
hiredate DATE,
sal NUMBER(7,2),
comm NUMBER(7,2),
deptno NUMBER(2,0),
CONSTRAINT PK_emp PRIMARY KEY (empno),
CONSTRAINT FK_deptno FOREIGN KEY (deptno) REFERENCES dept (deptno)
);
INSERT INTO emp VALUES (1698,'BLAKE','MANAGER',1839,'1981-5-1',2850,null,30);
INSERT INTO emp VALUES (1566,'JONES','MANAGER',1839, '1981-4-2',2975,null,40);
INSERT INTO emp VALUES (1788,'SCOTT','ANALYST',1566, '1987-7-15',3000,null,20);
INSERT INTO emp VALUES (1902,'FORD','ANALYST',1566, '1981-12-5',3000,null,40);
INSERT INTO emp VALUES (1369,'SMITH','CLERK',1902, '1980-12-17',800,null,20);
INSERT INTO emp VALUES (1499,'ALLEN','SALESMAN',1698, '1981-2-20',1600,300,30);
CREATE TABLE job_grades
(
grade_level VARCHAR(5),
lowest_sal INT,
highest_sal INT);
INSERT INTO job_grades VALUE ('A', 1000, 1999);
INSERT INTO job_grades VALUE ('B', 2000, 2999);
INSERT INTO job_grades VALUE ('C', 3000, 3999);
INSERT INTO job_grades VALUE ('D', 4000, 4999);
INSERT INTO job_grades VALUE ('E', 5000, 5999);
SELECT句とスカラーサブクエリの例
obclient> SELECT a.*,(SELECT count(*) FROM emp b WHERE b.deptno = a.deptno) AS Number of employees FROM dept a;
+--------+------------+-------------+---------------------+
| deptno | dname | location | Number of employees |
+--------+------------+-------------+---------------------+
| 20 | ACCOUNTING | Los Angeles | 2 |
| 30 | OPERATIONS | CHICAGO | 2 |
| 40 | SALES | NEW YORK | 2 |
+--------+------------+-------------+---------------------+
3 rows in set
obclient> SELECT (SELECT a.dname FROM dept a, emp b WHERE a.deptno = b.deptno AND b.empno = 1566) AS Department name;
+--------------+
| Department name |
+--------------+
| SALES |
+--------------+
1 row in set
FROM句とテーブルサブクエリの例
サブクエリの結果セットを1つのテーブルとして扱うためには、必ずエイリアスを付ける必要があります。例えば、以下の例ではテーブルのエイリアスが t1 と t2 です。
obclient> SELECT t1.deptno,sa AS '平均給与', t2.grade_level FROM (SELECT deptno,avg(a.sal) sa FROM emp a GROUP BY a.deptno) t1, job_grades t2 WHERE t1.sa BETWEEN t2.lowest_sal AND t2.highest_sal;
+--------+--------------+-------------+
| deptno | 平均給与 | grade_level |
+--------+--------------+-------------+
| 20 | 1900.000000 | A |
| 30 | 2225.000000 | B |
| 40 | 2987.500000 | B |
+--------+--------------+-------------+
3 rows in set
WHEREとHAVINGを含むサブクエリの例
スカラーサブクエリ
/*30号部門の最低賃金を上回る部門の部門番号とその最低賃金を取得するクエリを実行する*/ obclient> SELECT min(a.sal) minsalary,deptno FROM emp a GROUP BY a.deptno HAVING min(a.sal) > (SELECT min(sal) FROM emp WHERE deptno = 30); +-----------+--------+ | minsalary | deptno | +-----------+--------+ | 2975.00 | 40 | +-----------+--------+ 1 row in set列サブクエリは同じ列の複数行の結果セットを返します
/*jobがMANAGERの給与より低い他の職位の従業員の従業員番号、従業員名、職位、給与を返す*/ obclient> SELECT empname 従業員名, empno 従業員番号, job 職位, sal 給与 FROM emp WHERE sal < ALL (SELECT DISTINCT sal FROM emp WHERE job = 'MANAGER') AND job!= 'MANAGER'; +--------------+--------------+----------+---------+ | 従業員名 | 従業員番号 | 職位 | 給与 | +--------------+--------------+----------+---------+ | SMITH | 1369 | CLERK | 800.00 | | ALLEN | 1499 | SALESMAN | 1600.00 | +--------------+--------------+----------+---------+ 2 rows in set obclient> SELECT empname 従業員名, empno 従業員番号, job 職位, sal 給与 FROM emp WHERE sal < ALL (SELECT min(sal) FROM emp WHERE job = 'MANAGER') AND job!= 'MANAGER'; +--------------+--------------+----------+---------+ | 従業員名 | 従業員番号 | 職位 | 給与 | +--------------+--------------+----------+---------+ | SMITH | 1369 | CLERK | 800.00 | | ALLEN | 1499 | SALESMAN | 1600.00 | +--------------+--------------+----------+---------+ 2 rows in setサブクエリは1行複数列の結果セットを返します
obclient> SELECT * FROM emp a WHERE a.empno = (SELECT max(empno) FROM emp) AND sal = (SELECT max(sal) FROM emp); +-------+---------+---------+------+------------+---------+------+--------+ | empno | empname | job | mgr | hiredate | sal | comm | deptno | +-------+---------+---------+------+------------+---------+------+--------+ | 1902 | FORD | ANALYST | 1566 | 1981-12-05 | 3000.00 | NULL | 40 | +-------+---------+---------+------+------------+---------+------+--------+ 1 row in set
EXISTSと関連するサブクエリ
EXISTS サブクエリは、主クエリを先に実行して主クエリの結果を取得し、その後サブクエリに基づいてフィルタリングを行います。 これは、主クエリで使用されるフィールドに関連しているため、関連サブクエリと呼ばれます。EXISTS クエリの結果は1または0であり、サブクエリの結果セットに値が存在するかどうかを判断するために使用されます。一般的に、EXISTS サブクエリは IN で置き換えることができます。
例：
obclient> SELECT exists(SELECT empno FROM emp WHERE sal = 3000) AS 'existsは1または0を返します';
+----------------------+
| existsは1または0を返します |
+----------------------+
| 1 |
+----------------------+
1 row in set
obclient> SELECT dname FROM dept a WHERE exists(SELECT 1 FROM emp b WHERE a.deptno = b.deptno);
+------------+
| dname |
+------------+
| ACCOUNTING |
| OPERATIONS |
| SALES |
+------------+
3 rows in set
obclient> SELECT dname FROM dept a WHERE a.deptno IN (SELECT deptno FROM emp);
+------------+
| dname |
+------------+
| ACCOUNTING |
| OPERATIONS |
| SALES |
+------------+
3 rows in set