Zenn
Open6

【達人に学ぶSQL徹底指南書メモ】5. EXISTS述語の使い方

SQL
DB
churuchuruchuruchuru

5.1 EXISTS とは

  • 述語 = 真理値を返す関数
  • 他の述語と異なり、行の集合（SELECT 文）を引数に取る
    • 高階関数の一種と見なせる
  • 存在量化子
    • SQL には全称量化子が存在しないため、NOT EXISTS で代用する（ド・モルガンの法則）
churuchuruchuruchuru

5.2 テーブルに存在しないデータを探す

Meetings

meeting person
第 1 回 伊藤
第 1 回 水嶋
第 1 回 坂東
第 2 回 伊藤
第 2 回 宮田
第 3 回 坂東
第 3 回 水嶋
第 3 回 宮田

結果

meeting person
第 1 回 坂東
第 2 回 伊藤
第 2 回 宮田
第 3 回 坂東

5.2.1 存在量化の応用

SELECT DISTINCT
  M1.meeting,
  M2.person
FROM
  Meetings M1
  CROSS JOIN Meetings M2
WHERE
  NOT EXISTS(
    SELECT *
    FROM Meetings M3
    WHERE
      M1.meeting = M3.meeting
      AND M2.person = M3.person
  );

5.2.2 差集合演算の利用

SELECT DISTINCT
  M1.meeting,
  M2.person
FROM
  Meeting M1
  CROSS JOIN Meeting M2
EXCEPT
SELECT
  meeting,
  person
FROM
  Meeting;
churuchuruchuruchuru

5.3 全称量化（肯定 ⇔ 二重否定）

TestScores

student_id subject score
100 算数 100
100 国語 80
100 理科 80
200 算数 80
200 国語 95
300 算数 40
300 国語 90
300 社会 55
400 算数 80

すべての教科の点数が 50 点以上の生徒を選択する

すべての教科が 50 点以上 ⇔ 50 点未満である教科が 1 つも存在しない

SELECT DISTINCT
  student_id
FROM
  TestScores TS1
WHERE
  NOT EXISTS(
    SELECT *
    FROM TestScores TS2
    WHERE
      TS2.student_id = TS1.student_id
      AND TS2.score < 50
  );

結果

student_id
100
200
400

算数の点数が 80 点以上 and 国語の点数が 50 点以上の生徒を選択する

算数が 80 点以上 and 国語が 50 点以上 ⇔ 算数が 80 点未満 or 国語が 50 点未満でない

SELECT student_id
FROM TestScores TS1
WHERE
  NOT EXISTS(
    SELECT *
    FROM TestScores TS2
    WHERE
      TS2.student_id = TS1.student_id
      AND 1 = (
          CASE
            WHEN T2.subject = "算数" AND T2.score < 80 THEN 1
            WHEN T2.subject = "国語" AND T2.score < 50 THEN 1
            ELSE 0
          END
        )
  )
GROUP BY student_id
HAVING COUNT(*) = 2;

HAVING 句で必ず 2 教科そろっていることを条件に指定している。
student_id で GROUP BY しているので DISTINCT は不要。

結果

student_id
100
200
churuchuruchuruchuru

5.4 全称量化（HAVING vs NOT EXISTS）

Projects

project_id step_nbr status
AA100 0 完了
AA100 1 待機
AA100 2 待機
B200 0 待機
B200 1 待機
CS300 0 完了
CS300 1 完了
CS300 2 待機
CS300 3 待機
DY400 0 完了
DY400 1 完了
DY400 2 完了

工程 1 番まで完了のプロジェクトを選択する

5.4.1 HAVING（集合志向的な解答）

SELECT project_id
FROM Projects
GROUP BY project_id
HAVING
  COUNT(*) = SUM(
    CASE
      WHEN step_nbr <= 1 THEN AND status = "完了" THEN 1
      WHEN step_nbr > 1 AND status = "待機" THEN 1
      ELSE 0
    END
  );

結果

project_id
CS300

5.4.2 NOT EXISTS（述語論理的な解答）

SELECT *
FROM Projects P1
WHERE
  NOT EXISTS(
    SELECT *
    FROM Projects P2
    WHERE
      P1.project_id = P2.project_id
      AND P2.status <> (
          CASE
            WHEN step_nbr <= 1 THEN "完了"
            ELSE "待機"
          END
        )
  );

結果

project_id step_nbr status
CS300 0 完了
CS300 1 完了
CS300 2 待機
CS300 3 待機
デメリット
  • NOT EXISTS は二重否定を使う分、HAVING より直感的にわかりにくい
メリット
  • NOT EXISTS はパフォーマンスが良い
    • 1 行でも条件を満たさない行が存在すれば、そこで検索を打ち切る
    • project_id 列のインデックスも利用できる
  • NOT EXISTS は結果に含められる情報量が多い
    • HAVING は集約されるため、project_id しかわからない

5.4.3 ALL 述語による解答

SELECT *
FROM Projects P1
WHERE
  "T" = ALL(
    SELECT
      CASE
        WHEN step_nbr <= 1 AND status = "完了" THEN "T"
        WHEN step_nbr > 1 AND status = "待機" THEN "T"
        ELSE "F"
      END
    FROM Projects P2
    WHERE P1.project_id = P2.project_id
  );

結果

project_id step_nbr status
CS300 0 完了
CS300 1 完了
CS300 2 待機
CS300 3 待機
churuchuruchuruchuru

5.5 全称量化（行持ちの配列テーブル）

ArrayTbl2

key i val
A 1
A 2
A 3
... ... ...
A 10
B 1 3
B 2
B 3
... ... ...
B 10
C 1 1
C 2 1
C 3 1
... ... ...
C 10 1

オール 1 のエンティティを選択する

結果

key
C

NOT EXISTS による解答

SELECT DISTINCT key
FROMArrayTbl2 A1
WHERE
  NOT EXISTS(
    SELECT *
    FROM ArrayTbl2 A2
    WHERE
      A1.key = A2.key
      AND (A2.val <> 1 OR A2.val IS NULL)
  );

ALL 述語による解答

SELECT DISTINCT key
FROM ArrayTbl2 A1
WHERE
  1 = ALL(
    SELECT val
    FROM ArrayTbl2 A2
    WHERE A1.key = A2.key
  );

HAVING 句による解答

SELECT key
FROM ArrayTbl2
GROUP BY key
HAVING
  SUM(
    CASE
      WHEN val = 1 THEN 1
      ELSE 0
    END
  ) = 10;

HAVING 句で極値関数を使った解答

SELECT key
FROM ArrayTbl2
GROUP BY key
HAVING MAX(val) = 1 AND MIN(val) = 1;
churuchuruchuruchuru

5.6 数列を求める

Numbers

num
1
2
3
...
98
99
100

1 とその数以外に割り切れる正の整数が存在しない

SELECT num AS prime
FROM Numbers Dividend
WHERE
  num > 1
  AND NOT EXISTS(
    SELECT *
    FROM Numbers Divisor
    WHERE
      Divisor.num <= Dividend.num / 2
      AND Divisor.num <> 1
      AND MOD(Dividend.num, Divisor.num) = 0
  );

結果

prime
2
3
5
...
89
97