sql:全称量子の実装でexistsのバインドの条件による挙動の変化の理由がわからない

Question

表題の通りです。
以下のテーブルから、学部ごとにグルーピングした時にすべての行がnullでない学部を選択したいです。この例では経済学部と理学部を選択したいです。

私は以下のクエリで目的の経済学部と理学部を取得できると思っていました。
しかし実際には文学部が含まれています。
考えていたこととしては、
「相関サブクエリでdptごとにexistsを適用しよう(言語化不足)。
「あるdptにおいて、s1.sbmt_dateがnullであるという条件を満たさない行が一つもないdpt」である理学部と経済学部が以下のクエリで取得できるだろう」とかんがえていました。

実際に目的を達成できるコードは上記のコードからほんの少し変更した以下のコードです。

where句のあとのandの条件をs2に変更したものですが、これは正直自己結合だしどちらでも動作に違いがないだろうと思っていました。目的が達成できるコードとそうでないコードの挙動の違いはどこにあるのでしょうか。また、根本的に理解できていない点を指摘していただきたいです。よろしくお願いいたします。

コード

drop TABLE Students;
CREATE TABLE Students
(student_id   INTEGER PRIMARY KEY,
 dpt          VARCHAR(16) NOT NULL,
 sbmt_date    DATE);

INSERT INTO Students VALUES(100,  '理学部',   '2018-10-10');
INSERT INTO Students VALUES(101,  '理学部',   '2018-09-22');
INSERT INTO Students VALUES(102,  '文学部',   NULL);
INSERT INTO Students VALUES(103,  '文学部',   '2018-09-10');
INSERT INTO Students VALUES(200,  '文学部',   '2018-09-22');
INSERT INTO Students VALUES(201,  '工学部',   NULL);
INSERT INTO Students VALUES(202,  '経済学部', '2018-09-25');

select *
from students s1
WHERE not exists(select *
from students s2
WHERE s1.dpt = s2.dpt
and s2.sbmt_date is null
);

select *
from students

追記

Answer 1

どちらのクエリも

-- (Q1)
select *
from students s1;

という単純な形でこれは以下の結果になります(A)。

 student_id |   dpt    | sbmt_date  
------------+----------+------------
        100 | 理学部   | 2018-10-10
        101 | 理学部   | 2018-09-22
        102 | 文学部   | 
        103 | 文学部   | 2018-09-10
        200 | 文学部   | 2018-09-22
        201 | 工学部   | 
        202 | 経済学部 | 2018-09-25

これにサブクエリによる条件が付き

-- (Q2)
...
WHERE not exists(
--サブクエリ
...
);

のような形になっています。条件が満たされていれば上の各行は出力されるわけです。なので各行を1行ずつ見てそれぞれのクエリで条件が満たされるかどうか見ていきます。

正しい方のクエリの場合

サブクエリが

-- (Q3)
select *
from students s2
WHERE s1.dpt = s2.dpt
and s2.sbmt_date is null

のようになっています。では上の(A)の結果の1行目が条件を満たすか見ていきます。この行はdptが'理学部'なので、サブクエリは以下の形と等価になります。

-- (Q4)
select *
from students s2
WHERE '理学部' = s2.dpt
and s2.sbmt_date is null

このクエリの結果は

 student_id | dpt | sbmt_date 
------------+-----+-----------
(0 rows)

になります。not exsitsが条件なので、この行は見事に条件を満たしていることが分かります。同様に2行目はdptが'理学部'なので、上のクエリ(Q4)と同じになり、同じく条件を満たします。

次の3行目はdptが'文学部'なので、サブクエリは以下の形と等価になります。

-- (Q5)
select *
from students s2
WHERE '文学部' = s2.dpt
and s2.sbmt_date is null

このクエリの結果は

 student_id |  dpt   | sbmt_date 
------------+--------+-----------
        102 | 文学部 | 

になります。not exsitsが条件なので、この行は条件を満たしていないことが分かります。同様に4～5行目はdptが'文学部'なので、上のクエリ(Q5)と同じになり、同じく条件を満たしていません。

次の6行目はdptが'工学部'なので、サブクエリは以下の形と等価になります。

-- (Q6)
select *
from students s2
WHERE '工学部' = s2.dpt
and s2.sbmt_date is null

このクエリの結果は

 student_id |  dpt   | sbmt_date 
------------+--------+-----------
        201 | 工学部 |

になります。not exsitsが条件なので、この行は条件を満たしていないことが分かります。

次の7行目はdptが'経済学部'なので、サブクエリは以下の形と等価になります。

-- (Q7)
select *
from students s2
WHERE '経済学部' = s2.dpt
and s2.sbmt_date is null

このクエリの結果は

 student_id | dpt | sbmt_date 
------------+-----+-----------
(0 rows)

になります。not exsitsが条件なので、この行は条件を満たしていることが分かります。

結果、条件を満たす全行は

 student_id |   dpt    | sbmt_date  
------------+----------+------------
        100 | 理学部   | 2018-10-10
        101 | 理学部   | 2018-09-22
        202 | 経済学部 | 2018-09-25

となります。

間違っている方のクエリの場合

サブクエリが

-- (Q8)
select *
from students s2
WHERE s1.dpt = s2.dpt
and s1.sbmt_date is null

のようになっています。では上の(A)の結果の1行目が条件を満たすか見ていきます。この行はdptが'理学部'でsbmt_dateが'2018-10-10'なので、サブクエリは以下の形と等価になります。

-- (Q9)
select *
from students s2
WHERE '理学部' = s2.dpt
and '2018-10-10' is null

このクエリの結果は

 student_id | dpt | sbmt_date 
------------+-----+-----------
(0 rows)

になります。not exsitsが条件なので、この行は見事に条件を満たしていることが分かります。ただ、そもそも'2018-10-10' is nullの結果は明らかに偽なので、テーブルにアクセスすることなく結果が空であることが分かります。

2行目はdptが'理学部'でsbmt_dateが'2018-09-22'なので、サブクエリは以下の形と等価になります。

-- (Q10)
select *
from students s2
WHERE '理学部' = s2.dpt
and '2018-09-22' is null

このクエリの結果は

 student_id | dpt | sbmt_date 
------------+-----+-----------
(0 rows)

になります。not exsitsが条件なので、この行は見事に条件を満たしていることが分かります。1行目と同様ですね。

後はもう分かるとおり、s1のsbmt_dateがnullならs2にアクセスすることなくその行は出力されず、null以外ならs2のdptが必ず結合可能なため、出力されます。結果は

 student_id |   dpt    | sbmt_date  
------------+----------+------------
        100 | 理学部   | 2018-10-10
        101 | 理学部   | 2018-09-22
        103 | 文学部   | 2018-09-10
        200 | 文学部   | 2018-09-22
        202 | 経済学部 | 2018-09-25

のとおりです。つまりこの結果は、dptにnullが入らないなら以下と同じことになります。

select *
from students
WHERE sbmt_date is not null;

結論

サブクエリ側の表を対象としてsbmt_dateのnullを判定しないとサブクエリにする意味がない

Comments

[esklia]

ご回答ありがとうございます。言語化不足で恐縮ですが、
「s1.sbmt_date = s2.sbmt_dateとしたとき、
縛った方(s2のdpt)でグルーピングされておりs1のdptではグルーピングされていないので、そちらで条件指定してあげないと意味がない」
という感じでしょうか？

[esklia]

また、ご意見を聞いていて下記の記載が理解できないことに気づきました。
> s1のsbmt_dateがnullならs2にアクセスすることなくその行は出力されず、null以外ならs2のdptが必ず結合可能なため、出力されます。
おそらくs1.sumt_date = s2.sbmt_dateの部分の理解が「自己結合的なこと(自己結合ではない)をやっていてdpt単位でみるようにしている」という浅い理解にとどまっているためだと思います。大変恐縮ですが、この部分の挙動をもう少し詳しく教えていただけないでしょうか？

[退会済みユーザー]

> 「s1.sbmt_date = s2.sbmt_dateとしたとき、
> 縛った方(s2のdpt)でグルーピングされておりs1のdptではグルーピングされていないので、そちらで条件指定してあげないと意味がない」
> という感じでしょうか？

イメージがどうなっているのはよく分からないので、何とも言えません。

s1とs2はそれぞれwhere句で指定された条件で出力される行が決まるわけですが、s1もs2も互いのwhere句に別の表の内容が絡んでおり、単体ではどの行が出力されるのかが分からない状態です(なので相関なわけですが)。こういうケースでは一方の行を1行固定にすることで、他方のクエリから変数を外して固定値にし、イメージしやすくすることができる、なので、一方を1行ずつ固定して全行見ることで全体像を把握していくというのが今回の説明の狙いです。

なんでそういうことをしているかというと、一言で言えば言葉で説明しにくいからです。本来はこの相関クエリ自体が最も正確に何を出力したいのかを明快に語っているので。無理に言葉でまとめたとしてeskliaさんと私が共通の理解を持つに至る自信が私にはないということですね。なのでそのご質問にはお答えすることができません。

> > s1のsbmt_dateがnullならs2にアクセスすることなくその行は出力されず、null以外ならs2のdptが必ず結合可能なため、出力されます。
> おそらくs1.sumt_date = s2.sbmt_dateの部分の理解が「自己結合的なこと(自己結合ではない)をやっていてdpt単位でみるようにしている」という浅い理解にとどまっているためだと思います。大変恐縮ですが、この部分の挙動をもう少し詳しく教えていただけないでしょうか？

かなり冗長に説明したつもりなので、考え方を説明した以上後は自分で試して考えてもらいたいというのが本音なのですが...

Q10のクエリを見れば分かると思いますが、
'2018-09-22' is null
は定数なのです。SQLに偽を表す定数はないのですが、仮にコレをFALSEとすると
FALSE
と書いてよく、Q10は以下のように書き換えてもいいわけです。

select *
from students s2
WHERE '理学部' = s2.dpt
and FALSE

and条件でFALSEという固定値があるということは、s2.dptが何であれこのwhere句の結果は偽になります。つまり、このクエリは

select *
from students s2
WHERE FALSE

と書けるわけで、WHERE句が常に偽になるクエリなら結果はs2に触らずとも空だと分かります。なので、「s1のsbmt_dateがnullならs2にアクセスすることなくその行は出力されず」といえるわけです。
そして、s1のsbmt_dateがnull以外なら、Q10相当のクエリはどうなるかというと、今度は真を表す定数値を仮にTRUEとして、

select *
from students s2
WHERE '理学部' = s2.dpt
and TRUE

となるわけです。andで常に真がある条件というのはつまり何も書いてないのと同じなので、このクエリは以下と同じです。

select *
from students s2
WHERE '理学部' = s2.dpt

そして、今'理学部'となっている部分は元々s1.dptから取ってきた値なので、同じstudents表であるs2にもdptが'理学部'となる行が必ず存在します。すると、このクエリの結果は1行以上あるということになり、s1の該当行が出力されることが分かります。つまり、「null以外ならs2のdptが必ず結合可能なため、出力されます。」ということです。

[esklia]

ご親切に教えてくださりありがとうございます。
s1.sbmt_dateについて真偽判定する時とs2.subt_dateについて真偽判定するときの違いがよくわかりました。
s1.sbmt_dateについて真偽判定するならばexists句が必要なく、s2.sbmt_dateで絞り込んで判定すると文学部のときに
true and true and false=falseのように複数のレコードに対して判定されるので目的のクエリを取得できるということもよくわかりました。
おそらくこの違いが分かっていなかったことが理解を妨げていたのだと思います。

[退会済みユーザー]

> s1.sbmt_dateについて真偽判定するならばexists句が必要なく

正確な言い方をするなら、「exists句の中で(行データに依らない)固定条件になってしまう」というだけですね。今回はdptにnot null制約があるので、結果としてexists句が必要なくなります。

「true and true and false=falseのように」という記述は何のことかよく分かりませんが、固定値を意味しているのだと解釈しています。とにかく理解してもらえてよかったです。

[esklia]

「exists句の中で(行データに依らない)固定条件になってしまう」
こちらの方が適切な表現だと思います。親切に教えてくださり感謝申し上げます。

Answer 2

文学部の例で考えてみましょう。s1.sbmt_date is nullとした場合、
s1が4行目(IDが103)を見ているとしたとき、サブクエリ内では第一条件のs1.dpt = s2.dptの条件によって
3レコードの文学部が取れることになります。
そこからs1.sbmt_date is nullの条件によりIDが103の文学部のsbmt_dateはnullではありませんので、
条件は偽となり、サブクエリで取得できるレコードは0件となります。
そのため、not exists句は真となり、4行目(IDが103)の文学部のレコードは取得されることになります。
では、サブクエリの条件をs2.sbmt_date is nullにした場合を見てみましょう。
第一条件は同じなので3レコードの文学部が取れています。
そこからs2.sbmt_date is nullの条件により、取得できている3レコードの文学部のレコードから、
sbmt_dateがnullのレコードを取得します。この場合、IDが102の文学部のレコードが取得されます。
結果、not exists句は偽となり、4行目(IDが103)の文学部のレコードは取得されません。

違いは主問い合わせの結果1行毎に対して主問い合わせの日付を使ってサブクエリ内で絞り込んでいるかどうかということになります。

Comments

[esklia]

ご回答ありがとうございます。
> s1が4行目(IDが103)を見ているとしたとき、サブクエリ内では第一条件のs1.dpt = s2.dptの条件によって
3レコードの文学部が取れることになります。
そこからs1.sbmt_date is nullの条件によりIDが103の文学部のsbmt_dateはnullではありませんので、
条件は偽となり、サブクエリで取得できるレコードは0件となります。
そのため、not exists句は真となり、4行目(IDが103)の文学部のレコードは取得されることになります。

の中の
>そこからs1.sbmt_date is nullの条件によりIDが103の文学部のsbmt_dateはnullではありませんので、
条件は偽となり...4行目(IDが103)の文学部のレコードは取得されることになります。

に関連して、「existsは１行でも条件に合致する行を見つけたらそこで検索を打ち切る」という書籍の主張を思い出しました。nicorinpana様の主張はこの理由でしょうか？だとすると、レコードの順序が保証されていないのでid=103の行をたまたま見に行くか、id<>103の行を見に行くかで結果が変わってしまうように思えるのですが、、

[nicorinpana]

> existsは１行でも条件に合致する行を見つけたらそこで検索を打ち切る
これは今回の結果とは関係ありません。
おそらく質問者さんは相関副問い合わせの実行順について誤解されているような気がします。
相関副問い合わせは主問い合わせの結果1行ごとに副問い合わせが実行されます。
今回の例では主問い合わせの4行目について副問合せが実行された場合の例を挙げました。
5行目についても同じことが言えます。
副問合せの`s1.sbmt_date is null`という条件は主問い合わせの4行目についての副問合せの場合は`'2018-09-10' is null`という条件になりますし、5行目についてなら`2018-09-22 is null`という条件になるということです。

[esklia]

> 相関副問い合わせは主問い合わせの結果1行ごとに副問い合わせが実行されます。
これを把握しておりませんでした。ご指摘感謝申し上げます。追加で質問させていただきたいです。
質問下部に画像を追記させていただいたのですが、
①画像のようにdpt単位で
自己結合？みたいなことをして、結合の右側s2のsbmt_dateがis nullを満たさない行をnot existsについて取得する
という理解であっていますでしょうか？
②また「existsは１行でも条件に合致する行を見つけたらそこで検索を打ち切る」というのはどうせ見るのはs2.sbmt_dateなので
s1.dptで１つのレコードに対して結合してs2の条件を見ればよいので検索が打ち切られるということでしょうか？
お手数ですが、ご回答よろしくお願い致します。

[esklia]

>つまり、サブクエリ内の第一条件で絞り込んだ行に対して第二条件を適用しているかどうかの違いが動作の違いの理由になります。
③回答者様のこの表記を見るに、グルーピングではなくWHERE句の絞り込みを行っている。したがってS1とS2はそもそも結合されていない。かつ①、②で「結合みたいなことをして」と言っているのは間違っている。ということであっていますでしょうか？

[esklia]

申し訳ございません。質問を追加させていただきたいです。

④私はサブクエリ・相関サブクエリ・exists句について「SQL ゼロからはじめるデータベース操作」,「postgresql12.4文書」,「達人に学ぶSQL徹底指南書」を参照したのですが、回答者様の
>つまり、サブクエリ内の第一条件で絞り込んだ行に対して第二条件を適用しているかどうかの違いが動作の違いの理由になります。
という説明はどこにもなく、相関サブクエリでグルーピングする旨の主張がされていたことが誤解する原因になったと思っています(著者への非難の意図はありません)。別の情報源としてqiitaやブログには分かっている前提の歯抜けのような記事が多く、どう勉強すれば今回のような知識の抜けを防げるのか疑問です。熟練者はこの動作について当然のように知っていますが、この「知識の抜け」をどう防いでいるか是非伺いたいです。

[nicorinpana]

> グルーピングではなくWHERE句の絞り込みを行っている。したがってS1とS2はそもそも結合されていない
おっしゃるとおりです。group by を使っているわけでもなく、joinなどで結合しているわけでもないので、絞り込みしか行っていません。

> つまり、サブクエリ内の第一条件で絞り込んだ行に対して第二条件を適用しているかどうかの違いが動作の違いの理由になります
この表現は誤解を与えている気がするので訂正させてください。
第一条件に対してs1を使っている場合、第二条件はs2の問い合わせ内容に関わらずs1の取得内容だけで
決まっているという意図で書きましたが、よく読むとそんな意図が伝わる日本語になっていませんでした。
違いは主問い合わせの結果1行毎に対して主問い合わせの日付を使ってサブクエリ内で絞り込んでいるかどうかということになります。

[nicorinpana]

> どう勉強すれば今回のような知識の抜けを防げるのか疑問です。
非常に難しい問題です。
私自身も未だにその点は苦労しているところです。
体系的な知識を本で得るのはもちろんのこと、やはり実践してその中でつまずいてそれを調べたり、
教えてもらうことで一つ一つ理解していくしかないと思います。
私も問題に行き詰まったりした際は調べて動かして、理解を深めるように努めています。

[退会済みユーザー]

一応こちらにもコメントしておきますね。

今回の件は特別余計な知識が必要になる要件は含まれていないと思いますよ。書籍やWeb上の記事の説明が不適切なことは多いものの、そのせいばかりとは言えません。select文とwhere句、exists句の意味さえ理解できていれば、他の意味には取れないクエリだったと思います。SQLは手続き型の言語ではないので、処理順序を前提とした理解でない方が望ましいし、実際クエリの実行はその順で行われないことがあります。

個人的には、SQLという言語だけで説明できる範囲内で体系的な知識が必要だとは思いません。
パフォーマンスを気にし始めたり、トランザクションやデータベースの内部構造の概念的な理解をしたいと思ったり、最新の技術動向を把握したかったり、自分でデータベースのようなものを実装する場合には、事前にある程度体系的な知識もあった方がいいと思います。

※個人的な見解であり熟練者の意見ではありません

[esklia]

ありがとうございます。心にとめてきちんとコードを読んで勉強しなおします。