EXISTSを理論的に学ぶ - xxyoko10のブログ

はじめに

ブログでEXISTSについて、取り上げましたが理論的なことには触れていませんでした。本に解説があったので、整理するために今回の記事を書きます。

■参考書籍

達人に学ぶSQL徹底指南書第2版初級者で終わりたくないあなたへ (CodeZine BOOKS)

作者:ミック
出版社/メーカー: 翔泳社
発売日: 2018/10/11
メディア: 単行本（ソフトカバー）

SQLの述語とは

まず、EXISTSの特徴を取り上げる前に、SQLの述語について理解しておく必要があります。
SQLの述語は戻り値が真理値になる関数のことを指します。
BETWEEN、LIKE、IN、=、>、<とかです。
すべて戻り値はtrue、false、unknowになります。
SUMやAVGは、含まれないです。
EXISTSは述語ですが、BETWEENやINなど他の述語とは異なります。
それは『引数』です。

SELECT * FROM TABLE
 WHERE Colum  BETWEEN 100 AND 200;

SELECT * FROM TABLE
 WHERE Colum IN ('100','150');

SELECT * FROM TABLE_A A
  WHERE EXISTS
              (SELECT * FROM TABLE_B B
                 WHERE A.id = B.id);

上のSQLを見ていただければわかると思います。
BETWEEN とIN は引数は「150」とか単一の値をとります。
しかしEXISTSの引数はSELECT文です。
EXISTSはサブクエリがどういう列を選択するかは気にしません。
EXISTSのサブクエリのSELECT句のリストには3通りの書き方があります。

１．ワイルドカード：SELECT *
２．定数：SELECT 'ここは何でもいいんだよ'
３．列名：SELECT col

EXISTSは階層が異なる

以上のことを踏まえて、以下の図を見てください。
■引数は1行 f:id:xxyoko10:20200204215037p:plain

■引数は行の集合 f:id:xxyoko10:20200204215057p:plain

1行を入力としているLIKEやBETWEENなどの述語を「1階の述語」、
行の集合を入力としている述語を「2回の述語」と言います。

一階の述語　＝　「1行」を入力に取る述語

二階の述語　＝　「行の集合」を入力に取る述語

EXISTSは他の関数とは異なり、複数行を一単位とみなした高度な条件を記述することができるのです。

述語論理について

SQLの基礎理論には述語論理があります。

述語論理には、文を書くための道具である量化子という述語を用いた、量化理論があります。
EXISTS述語は量化理論で表現することができます

量化子には、全称量化子と存在量化子があり、まとめると以下のようになります。

「すべてのｘが条件Pを満たす」　　　　　　　　　　・・・全称量化子

「条件Pを満たすｘが（少なくとも1つ）存在する」　　・・・存在量化子

ここで押さえておかなくてはならないのは、
EXISTSは存在量化子を実装したものであるということです。
SQLは全称量化子に対応する述語を導入しませんでした。
そのため、全称量化子を表現するためには、「すべてのｘが条件Pを満たす」を
「条件Pを満たさない行が存在しない」に変換する必要があります。

量化理論の実践編

量化理論がEXISTS述語でどう利用されているか具体例を用いて考えます。

CREATE TABLE TestScores
(student_id INTEGER,
 subject    VARCHAR(32) ,
 score      INTEGER,
  PRIMARY KEY(student_id, subject));

INSERT INTO TestScores VALUES(100, '算数',100);
INSERT INTO TestScores VALUES(100, '国語',80);
INSERT INTO TestScores VALUES(100, '理科',80);
INSERT INTO TestScores VALUES(200, '算数',80);
INSERT INTO TestScores VALUES(200, '国語',95);
INSERT INTO TestScores VALUES(300, '算数',40);
INSERT INTO TestScores VALUES(300, '国語',90);
INSERT INTO TestScores VALUES(300, '社会',55);
INSERT INTO TestScores VALUES(400, '算数',80);

■TestScores
f:id:xxyoko10:20200209193947p:plain

作成した TestScoresテーブルを用いて、「すべての行について～」という全称量化の表現を、
「～でない行が一つも存在しない」という二重否定文へ変換する技術を学びます。

「すべての教科について50点以上を取っている生徒を選択してください」という問題を、
全称量化を用いて表現すると、

すべての教科が50点以上である

となり、これを存在量化に変換すると、

50点未満である教科が1つも存在しない

になります。

これをNOT　EXISTSで表現します。

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); --50 点未満の教科

■結果
f:id:xxyoko10:20200209201136p:plain