副問合せ SUBQUERY

副問合せは一般にパフォーマンスが悪いのでなるべく使わない方が良い。他に回避方法がないときに限り消極的に使用する。副問合せを多用しなければならないシステムはパフォーマンスの悪さが懸念される。テーブル設計を疑うべし。

```SQL:準備
CREATE TABLE PARENTS  -- 親
    (
    PARENT_ID    NUMBER(11) NOT NULL  -- 親ID
  , PARENT_NAME  VARCHAR2(256)        -- 親の名前
  , CONSTRAINT PARENTS_PK PRIMARY KEY(PARENT_ID) USING INDEX  -- PRIMARY KEY
    ) ;

CREATE TABLE CHILDREN  -- 子
    (
    PARENT_ID   NUMBER(11) NOT NULL  -- 親ID
  , CHILD_ID    NUMBER(2)  NOT NULL  -- 子ID
  , CHILD_NAME  VARCHAR2(256)        -- 子の名前
  , CONSTRAINT CHILDREN_PK PRIMARY KEY(PARENT_ID, CHILD_ID) USING INDEX  -- PRIMARY KEY
    ) ;
```
```SQL:データを用意(パフォーマンスの調査には実際のデータに見合うデータ量を確保する)
INSERT INTO PARENTS(PARENT_ID, PARENT_NAME) VALUES(1, 'Padme Amidala Naberrie') ;
INSERT INTO PARENTS(PARENT_ID, PARENT_NAME) VALUES(2, 'Anakin Skywalker') ;
INSERT INTO CHILDREN(PARENT_ID, CHILD_ID, CHILD_NAME) VALUES(1, 1, 'Luke Skywalker') ;
INSERT INTO CHILDREN(PARENT_ID, CHILD_ID, CHILD_NAME) VALUES(1, 2, 'Leia Organa') ;
INSERT INTO CHILDREN(PARENT_ID, CHILD_ID, CHILD_NAME) VALUES(5, 1, '(Han Solo') ;
COMMIT ;
```
とテーブルがあったとして、副問合せの種類

-- (1) EXISTS, NOT EXISTS 方式

```SQL:EXISTS
SELECT
    CS.PARENT_ID
  , CS.CHILD_ID
FROM CHILDREN CS
WHERE NOT EXISTS
    (
    SELECT
        PS.PARENT_ID
    FROM PARENTS PS
    WHERE PS.PARENT_ID = CS.PARENT_ID
    )
ORDER BY
    CS.PARENT_ID
  , CS.CHILD_ID ;

    PARENT_ID      CHILD_ID
------------- -------------
            5             1

1行が選択されました。
```
```SQL:実行計画
SQL> explain plan for
  2  SELECT
  3      CS.PARENT_ID
  4    , CS.CHILD_ID
  5  FROM CHILDREN CS
  6  WHERE NOT EXISTS
  7      (
  8      SELECT
  9          PS.PARENT_ID
 10      FROM PARENTS PS
 11      WHERE PS.PARENT_ID = CS.PARENT_ID
 12      )
 13  ORDER BY
 14      CS.PARENT_ID
 15    , CS.CHILD_ID ;

解析されました。

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY()) ;

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------
Plan hash value: 3979919563

----------------------------------------------------------------------------------
| Id  | Operation          | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |             |     2 |    18 |     1   (0)| 00:00:01 |
|   1 |  NESTED LOOPS ANTI |             |     2 |    18 |     1   (0)| 00:00:01 |
|   2 |   INDEX FULL SCAN  | CHILDREN_PK |     3 |    18 |     1   (0)| 00:00:01 |
|*  3 |   INDEX UNIQUE SCAN| PARENTS_PK  |     1 |     3 |     0   (0)| 00:00:01 |
----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("PS"."PARENT_ID"="CS"."PARENT_ID")

15行が選択されました。
```

-- (2) IN, NOT IN 方式

```SQL:IN
SELECT
    CS.PARENT_ID
  , CS.CHILD_ID
FROM CHILDREN CS
WHERE CS.PARENT_ID NOT IN
    (
    SELECT
        PS.PARENT_ID
    FROM PARENTS PS
    )
ORDER BY
    CS.PARENT_ID
  , CS.CHILD_ID ;

    PARENT_ID      CHILD_ID
------------- -------------
            5             1

1行が選択されました。
```
   外側のWHERE句に列があり、IN or NOT IN  が記述される。SUBQUERYの中のWHERE句がなくなる(他に条件がある場合を除く)

```SQL:実行計画
SQL> explain plan for
  2  SELECT
  3      CS.PARENT_ID
  4    , CS.CHILD_ID
  5  FROM CHILDREN CS
  6  WHERE CS.PARENT_ID NOT IN
  7      (
  8      SELECT
  9          PS.PARENT_ID
 10      FROM PARENTS PS
 11      )
 12  ORDER BY
 13      CS.PARENT_ID
 14    , CS.CHILD_ID ;

解析されました。

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY()) ;

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------
Plan hash value: 3979919563

----------------------------------------------------------------------------------
| Id  | Operation          | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |             |     2 |    18 |     1   (0)| 00:00:01 |
|   1 |  NESTED LOOPS ANTI |             |     2 |    18 |     1   (0)| 00:00:01 |
|   2 |   INDEX FULL SCAN  | CHILDREN_PK |     3 |    18 |     1   (0)| 00:00:01 |
|*  3 |   INDEX UNIQUE SCAN| PARENTS_PK  |     1 |     3 |     0   (0)| 00:00:01 |
----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("CS"."PARENT_ID"="PS"."PARENT_ID")

15行が選択されました。
```
-- (3) 相関副問合せ : イコール、NOT イコール方式 =, <> (or !=)

```SQL:相関副問合せ
SELECT
    CS.PARENT_ID
  , CS.CHILD_ID
FROM CHILDREN CS
WHERE CS.PARENT_ID <>
    (
    SELECT
        PS.PARENT_ID
    FROM PARENTS PS
    WHERE PS.PARENT_ID = 1
    )
ORDER BY
    CS.PARENT_ID
  , CS.CHILD_ID ;

    PARENT_ID      CHILD_ID
------------- -------------
            5             1

1行が選択されました。
```
```SQL:実行計画
SQL> explain plan for
  2  SELECT
  3      CS.PARENT_ID
  4    , CS.CHILD_ID
  5  FROM CHILDREN CS
  6  WHERE CS.PARENT_ID <>
  7      (
  8      SELECT
  9          PS.PARENT_ID
 10      FROM PARENTS PS
 11      WHERE PS.PARENT_ID = 1
 12      )
 13  ORDER BY
 14      CS.PARENT_ID
 15    , CS.CHILD_ID ;

解析されました。

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY()) ;

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------
Plan hash value: 2518209649

----------------------------------------------------------------------------------
| Id  | Operation          | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |             |     2 |    12 |     1   (0)| 00:00:01 |
|*  1 |  INDEX FULL SCAN   | CHILDREN_PK |     2 |    12 |     1   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN| PARENTS_PK  |     1 |     3 |     0   (0)| 00:00:01 |
----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("CS"."PARENT_ID"<> (SELECT "PS"."PARENT_ID" FROM "PARENTS"
              "PS" WHERE "PS"."PARENT_ID"=1))
   2 - access("PS"."PARENT_ID"=1)

16行が選択されました。
```

```SQL:エラー
SQL> SELECT
  2      CS.PARENT_ID
  3    , CS.CHILD_ID
  4  FROM CHILDREN CS
  5  WHERE CS.PARENT_ID <>
  6      (
  7      SELECT
  8          PS.PARENT_ID
  9      FROM PARENTS PS
 10      )
 11  ORDER BY
 12      CS.PARENT_ID
 13    , CS.CHILD_ID ;
    SELECT
    *
行7でエラーが発生しました。:
ORA-01427: 単一行副問合せにより2つ以上の行が戻されます
```
      相関副問合せではインラインビューでのSELECT結果が2行以上あるとエラーになる。

-- (4) スカラー副問合せ  since Oracle9.0.1
後日追加