サブクエリ

サブクエリとは、上位のクエリにネストされたクエリを指します。SQL言語は多層ネストされたクエリを許可しています。つまり1つのサブクエリの中に他のサブクエリをネストすることができます。

サブクエリの紹介

サブクエリとは、SELECT クエリステートメントの中に1つまたは複数の SELECT ステートメントがさらにネストされていることを指し、単一行または複数行の結果を返す場合や、実行結果を返さない場合があります。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_ny、 dept_ny、job_grades_ny を作成し、データを挿入します。

1. dept_ny テーブルを作成します。

   obclient> CREATE TABLE dept_ny(
              deptno           NUMBER(2,0),
              dname            VARCHAR(14),
              location         VARCHAR(13),
              CONSTRAINT pk_dept PRIMARY KEY(deptno)
             );
   Query OK, 0 rows affected
   

2. job_grades_ny テーブルを作成します。

   obclient> CREATE TABLE job_grades_ny
             (
             grade_level VARCHAR(5),
             lowest_sal NUMBER,
             highest_sal NUMBER);
   Query OK, 0 rows affected
   

3. emp_ny テーブルを作成します。

   obclient> CREATE TABLE emp_ny(
              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),
              age           NUMBER(2,0),
              CONSTRAINT PK_emp PRIMARY KEY (empno),
              CONSTRAINT FK_deptno  FOREIGN KEY (deptno)  REFERENCES dept_ny (deptno)
              );
   Query OK, 0 rows affected
   

4. dept_ny テーブルにデータを挿入します。

   obclient> INSERT INTO dept_ny VALUES
             (20,'Finance','beijing'),
             (35,'Administration','hangzhou'),
             (40,'Development','xian'),
             (30,'Workshop','guangzhou'),
             (25,'Legal affairs','shanghai'),
             (45,'Office','suzhou');
   Query OK, 6 rows affected
   Records: 6  Duplicates: 0  Warnings: 0
   

5. job_grades_ny テーブルにデータを挿入します。

   obclient> INSERT INTO job_grades_ny VALUES ('A', 1000, 1999),
             ('B', 2000, 2999),('C', 3000, 3999),('D', 4000, 4999),
             ('E', 5000, 5999);
   Query OK, 5 rows affected
   Records: 5  Duplicates: 0  Warnings: 0
   

6. emp_ny テーブルにデータを挿入します。

   obclient> INSERT INTO emp_ny VALUES
               (1369,'SMITH','CLERK',1902,DATE'1980-12-17',800.00,NULL,20,22),
               (1499,'ALLEN','SALESMAN',1698,DATE'1981-02-20',1600.00,300.00,35,22),
               (1566,'JONES','MANAGER',1839,DATE'1981-04-02',2975.00, NULL,40,22),
               (1698,'BLAKE' ,'MANAGER',1839,DATE'1981-05-01',2850.00,NULL ,30,33),
               (1788,'SCOTT','ANALYST',1566,DATE'1987-07-15',3000.00,NULL ,25,33),
               (1902,'FORD','ANALYST',1566,DATE'1981-12-05',3000.00, NULL,45,22);
   Query OK, 6 rows affected
   Records: 6  Duplicates: 0  Warnings: 0
   

例1：SELECT句とスカラーサブクエリ

*スカラーサブクエリを使用して各部門の従業員数を取得するクエリを実行します。

obclient> SELECT a.*,(SELECT count(*) FROM emp_ny b WHERE b.deptno = a.deptno) AS従業員数FROM dept_ny a;
+--------+----------------+-----------+--------------+
| DEPTNO | DNAME          | LOCATION  | 従業員数     |
+--------+----------------+-----------+--------------+
|     20 | Finance        | beijing   |            1 |
|     25 | Legal affairs  | shanghai  |            1 |
|     30 | Workshop       | guangzhou |            1 |
|     35 | Administration | hangzhou  |            1 |
|     40 | Development    | xian      |            1 |
|     45 | Office         | suzhou    |            1 |
+--------+----------------+-----------+--------------+
6 rows in set

*スカラーサブクエリを使用して empno=1566 に対応する部門を取得するクエリを実行します。

obclient> SELECT (SELECT a.dname FROM dept_ny a, emp_ny b WHERE a.deptno = b.deptno AND b.empno = 1566) AS部門の名前FROM emp_ny b;
+--------------+
| 部門の名前     |
+--------------+
| Development  |
| Development  |
| Development  |
| Development  |
| Development  |
| Development  |
+--------------+
6 rows in set

例2：WHEREとHAVINGを含むサブクエリ

• スカラーサブクエリ

  WHERE と HAVING のサブクエリを使用して、30号部門の最低賃金を上回る部門の部門番号とその最低賃金を取得するクエリを実行します。

  obclient> SELECT min(a.sal) minsalary,deptno FROM emp_ny a GROUP BY a.deptno
           HAVING min(a.sal) > (SELECT min(sal) FROM emp_ny WHERE deptno = 30);
  +-----------+--------+
  | MINSALARY | DEPTNO |
  +-----------+--------+
  |      3000 |     25 |
  |      2975 |     40 |
  |      3000 |     45 |
  +-----------+--------+
  3 rows in set
  

• 列サブクエリは同じ列の複数行の結果セットを返します

  列サブクエリを使用して、job が MANAGER のすべての給与より低い他の職位の従業員の従業員番号、従業員名、職位、および給与を返します。

  obclient> SELECT empname 従業員名, empno 従業員番号, job 職位, sal 給与 FROM emp_ny 
           WHERE sal < ALL
           (SELECT DISTINCT sal FROM emp_ny WHERE job = 'MANAGER') AND job!= 'MANAGER';
  +--------------+--------------+----------+--------+
  | 従業員名     | 従業員番号     | 職位     | 給与   |
  +--------------+--------------+----------+--------+
  | SMITH        |         1369 | CLERK    |    800 |
  | ALLEN        |         1499 | SALESMAN |   1600 |
  +--------------+--------------+----------+--------+
  2 rows in set
  

  列サブクエリ + 集約関数 min を使用して、job が MANAGER のすべての給与より低い他の職位の従業員の従業員番号、従業員名、職位、および給与を返します。

  obclient> SELECT empname 従業員名, empno 従業員番号, job 職位, sal 給与 FROM emp_ny
           WHERE sal < ALL (SELECT min(sal) FROM emp_ny WHERE job = 'MANAGER') AND job!= 'MANAGER';
  +--------------+--------------+----------+--------+
  | 従業員名      | 従業員番号     | 職位      | 給与   |
  +--------------+--------------+----------+--------+
  | SMITH        |         1369 | CLERK    |    800 |
  | ALLEN        |         1499 | SALESMAN |   1600 |
  +--------------+--------------+----------+--------+
  2 rows in set
  

• サブクエリは1行複数列の結果セットを返します

  行サブクエリを使用して、empno が最大で、かつ給与が最も高い従業員を取得するクエリを実行します。

  obclient> SELECT * FROM emp_ny a 
           WHERE a.empno = (SELECT max(empno) FROM emp_ny) 
           AND sal = (SELECT max(sal) FROM emp_ny);
  +-------+---------+---------+------+-----------+------+------+--------+------+
  | EMPNO | EMPNAME | JOB     | MGR  | HIREDATE  | SAL  | COMM | DEPTNO | AGE  |
  +-------+---------+---------+------+-----------+------+------+--------+------+
  |  1902 | FORD    | ANALYST | 1566 | 05-DEC-81 | 3000 | NULL |     45 |   22 |
  +-------+---------+---------+------+-----------+------+------+--------+------+
  1 row in set
  

例3：EXISTSと関連するサブクエリ

EXISTS サブクエリは、まず主クエリを実行して主クエリの結果を取得し、その後サブクエリに基づいてフィルタリングを行います。サブクエリには主クエリで使用されるフィールドが含まれているため、関連サブクエリと呼ばれます。EXISTS クエリの結果は1または0であり、サブクエリの結果セットに値が存在するかどうかを判断するために使用されます。一般的に、EXISTS サブクエリは IN で置き換えることができます。

• EXISTS句により、dept_ny テーブルと emp_ny の deptno が同じ DNAME データを取得するクエリを実行します。

  obclient> SELECT dname FROM dept_ny a WHERE EXISTS(SELECT 1 FROM emp_ny b WHERE a.deptno = b.deptno);
  +----------------+
  | DNAME          |
  +----------------+
  | Finance        |
  | Legal affairs  |
  | Workshop       |
  | Administration |
  | Development    |
  | Office         |
  +----------------+
  6 rows in set
  

• IN により、dept_ny テーブルと emp_ny の deptno が同じ DNAME データを取得するクエリを実行します。

  obclient> SELECT dname FROM dept_ny a WHERE a.deptno IN (SELECT deptno FROM emp_ny);
  +----------------+
  | DNAME          |
  +----------------+
  | Finance        |
  | Legal affairs  |
  | Workshop       |
  | Administration |
  | Development    |
  | Office         |
  +----------------+
  6 rows in set
  

例4：MULTISET（subquery）サブクエリ

CAST(MULTISET (subquery)) AS type_name の結果はセット型であり、サブクエリによって生成された行が対応するセット要素に変換されます。例：

obclient [USER001]> CREATE TYPE cust_tbl2 AS TABLE OF varchar2(20);
Query OK, 0 rows affected

obclient [USER001]> CREATE TABLE tbl1(col1 INT, col2 INT);
Query OK, 0 rows affected

obclient [USER001]> CREATE TABLE tbl2(col1 INT, col2 INT);
Query OK, 0 rows affected

obclient [USER001]> INSERT INTO tbl1 VALUES (1,1);
Query OK, 1 row affected

obclient [USER001]> INSERT INTO tbl2 VALUES (1,2),(1,3);
Query OK, 2 rows affected
 Records: 2  Duplicates: 0  Warnings: 0

obclient [USER001]> SELECT * FROM tbl1,TABLE(CAST(MULTISET(SELECT tbl2.col2 FROM tbl2 WHERE tbl1.col1=tbl2.col1) AS cust_tbl2));
+------+------+--------------+
| COL1 | COL2 | COLUMN_VALUE |
+------+------+--------------+
|    1 |    1 | 2            |
|    1 |    1 | 3            |
+------+------+--------------+
2 rows in set

関連ドキュメント

• SELECT
• CAST
• CREATE TYPE