サブクエリ

サブクエリとは、上位のクエリ内に埋め込まれたクエリを指します。SQL言語では、複数層のネストされたクエリが許可されており、つまり1つのサブクエリの中にさらに他のサブクエリをネストすることも可能です。

サブクエリの概要

サブクエリとは、SELECT 文の中に別の SELECT 文を1つ以上ネストしたもので、単一行、複数行の結果を返す場合もあれば、結果を返さない場合もあります。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', 40000, 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
  

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

  列サブクエリを使用して、職位 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