SQL DML:副問い合わせ

概要

物理的に実在する表、行、列でなく、論理的に作成した表、行、列を利用する問い合わせ。
不定の値に基づいてデータを検索できる。

副問い合わせ可能な「句」

・SELECT文
SELECT
 (SELECT 列名 FROM ~)
FROM ~

・FROM句
SELECT
 列名1 , 列名2
FROM
 (SELECT 列名1 , 列名2 FROM ~)

・WHERE句
SELECT ~ FROM ~
WHERE
 (SELECT 列名1 FROM ~) = ~

・HAVING
SELECT ~ FROM ~ GROUP BY ~
HAVING
 (SELECT ~ FROM ~) = ~

SELECT句、FROM句、WHERE句、ORDER BY句で利用可能

SELECT
  int,
  alf,
  kana,
  ( SELECT MAX(int) FROM [Table1] ) AS MaxInt
FROM
  [Table1]

テーブル:Table1&2
int alf kana MaxInt
Record1 1 a あ 6
Record2 2 b い 6
Record3 3 c う 6
Record4 4 あ 6
Record5 5 い 6
Record6 6 う 6

副問い合わせ結果を用いたデータ操作

【INSERT文】
INSERT INTO 表名 SELECT ~ FROM ~
副問い合わせではINSERT先表の全列を指定する必要あり

【UPDATE文】
UPDATE 表名 SET (列名1 , 列名2) = (SELECT 列名1 , 列名2 FROM ~ WHERE ~) WHERE ~

【DELETE文】
DELETE FROM 表名 (列名1 , 列名2) IN (SELECT 列名1 , 列名2 FROM ~ WHERE ~)
DELETE FROM 表名 (列名1 , 列名2) ALL (SELECT 列名1 , 列名2 FROM ~ WHERE ~)

【CREATE文】
CREATE TABLE 表名 AS SELECT ~ FROM ~
CREATE VIEW 表名 AS SELECT ~ FROM ~

【UPDATE文】
更新先とサブクエリが同じテーブルの場合
×
UPDATE 表名 SET 列名1 = (SELECT 列名1 FROM ~ WHERE ~) WHERE ~


UPDATE 表名, (SELECT sub列名 FROM ~ WHERE ~) AS sub
SET 列名1 = sub.sub列名
WHERE ~

単一行(列)問い合わせ

最高給与額取得者を問い合わせる
SELECT temp.氏名
FROM 給与
WHERE 給与.給与額 = (SELECT MAX(給与額) FROM 給与)
※副問い合わせ結果が複数行ある様な問い合わせはエラー
SELECT temp.氏名
FROM 給与
WHERE 給与.給与額 = (SELECT 給与額 FROM 給与 WHERE 給与 > 500,000)

複数行(列)問い合わせ

特定の部門に所属する社員を問い合わせる

IN句

SELECT * FROM 社員
WHERE 職務 IN (SELECT 職務 FROM 社員 WHERE 部門 = 10)

ANY句

リストの最小値より大きい
SELECT * FROM 社員
WHERE 給与 >= ANY (SELECT 給与 FROM 社員 WHERE 部門 = 10)

リストの最大値より小さい
SELECT * FROM 社員
WHERE 給与 <= ANY (SELECT 給与 FROM 社員 WHERE 部門 = 10)

リストのいずれかを含む
SELECT * FROM 社員
WHERE 職務 = ANY (SELECT 職務 FROM 社員 WHERE 部門 = 10)
WHERE 職務 IN (SELECT 職務 FROM 社員 WHERE 部門 = 10) と同じ

否定
WHERE ~ NOT > ANY (~) = WHERE ~ <= ALL (~) WHERE ~ NOT < ANY (~) = WHERE ~ >= ALL (~)

ALL句

リストの最大値より大きい
SELECT * FROM 社員
WHERE 給与 >= ALL (SELECT 給与 FROM 社員 WHERE 部門 = 10)

リストの最小値より小さい
SELECT * FROM 社員
WHERE 給与 <= ALL (SELECT 給与 FROM 社員 WHERE 部門 = 10)

NOT IN (!= ALL)

列1 列2
1  A
2  B
3  NULL

SELECT 列1 FROM 表 WHERE 列2 IS NULL
→ 3

SELECT 列1 FROM 表 WHERE 列2 IN (NULL)
→ NULL (該当無し)

SELECT 列1 FROM 表 WHERE 列2 IN (SELECT 列2 FROM 表)
SELECT 列1 FROM 表 WHERE 列2 IN (‘A’, ‘B’, NULL)
→ 1
→ 1

SELECT 列1 FROM 表 WHERE 列2 NOT IN (‘A’, ‘B’, NULL)
SELECT 列1 FROM 表 WHERE 列2 != ALL (SELECT 列2 FROM 表)
SELECT 列1 FROM 表 WHERE 列2 != ALL (‘A’, ‘B’, NULL)
→ NULL (該当無し)

相関副問合せ(EXISTS/NOT EXISTS)

【EXISTS】
SELECT * FROM 商品M
WHERE EXISTS (
 SELECT 売上ID FROM 売上 WHERE 売上.売上ID = 商品M.商品ID
);



↓結果

フロー
1)主問い合わせ(SELECT * FROM 商品M) を実行
2)1の結果をカーソルの様に使用して全件Loop。内1レコード対して、
 副問い合わせ(SELECT 売上ID FROM 売上 WHERE 売上.売上ID = 商品M.商品ID) を実行
3)2の結果を判定

フロー説明
1)主問い合わせ(SELECT * FROM 商品M)を実行

2) 1レコード目:主問い合わせの1レコード目に対して、副問い合わせ
 (SELECT 売上ID FROM 売上 WHERE 売上.売上ID = 商品M.商品ID)を実行
 ※1レコード目の場合は、商品M.商品IDは’1′

3) 1レコード目:2の結果を判定

→2の副問い合わせ結果は、「真」である為、結果、主問い合わせの1レコード目はSELECTされる

2) 2レコード目:主問い合わせの2レコード目に対して、副問い合わせ
 (SELECT 売上ID FROM 売上 WHERE 売上.売上ID = 商品M.商品ID)を実行
 ※2レコード目の場合は、商品M.商品IDは’2′

3) 2レコード目:2の結果を判定

→2の副問い合わせ結果は、「偽」である為、結果、主問い合わせの2レコード目はSELECTされない

【NOT EXISTS】
SELECT * FROM 商品M
WHERE NOT EXISTS (
 SELECT 売上ID FROM 売上 WHERE 売上.売上ID = 商品M.商品ID
);



↓結果

共通テーブル

≠一時テーブル
Create/Dropは不要
≒Join

WITH CommonTableA AS
(
  SELECT
    FieldA,
    FieldB,
    FieldC
  FROM
    TestTableA
),
CommonTableB AS
(
  SELECT
    FieldA,
    FieldB,
    FieldC
  FROM
    TestTableB
)

SELECT
  CommonTableA.FieldA,
  CommonTableA.FieldB,
  (SELECT FieldC FROM CommonTableB WHERE FieldA = CommonTableA.FieldA) AS FieldC
FROM
  CommonTableA

;
WITH CommonTableA AS
(
  SELECT
    FieldA,
    FieldB,
    FieldC
  FROM
    TestTableA
),
CommonTableB AS
(
  SELECT
    FieldA,
    FieldB,
    FieldC
  FROM
    TestTableB
)

SELECT
  CommonTableA.FieldA,
  CommonTableA.FieldB,
  (SELECT FieldC FROM CommonTableB WHERE FieldA = CommonTableA.FieldA) AS FieldC
FROM
  CommonTableA