EXIST NOT EXSIT

Question

一つ前のSQLに関する質問です。リンク内容
以前の質問で以下のSQL文を回答としていただきました。

select hotel_id from hotels a where not exists(
select 1 from tags c where c.tag in ('朝食付き','キャンセル無料') and not exists(
select 1 from hotels_tags b where c.tags_id = b.tags_id and b.hotel_id = a.hotel_id));

このSQLの挙動が理解できずに困っております。分解するために以下のSQLを実行しました。

              select * from hotels a where　exists(
  select 1 from tags c where c.tag in ('朝食付き','キャンセル無料'));

質問❶
しかしtagsテーブルにはhotelテーブルと紐付けできるものがないのでこれだと、inの中にtagsに存在するタグ名(WiFi無料、プール、朝食付きなど)を記入すればtrueとなり全てを取得するということでしょうか。

/*NOTを追加*/
              select * from hotels a where not exists(
  select 1 from tags c where c.tag in ('朝食付き','キャンセル無料'));

質問❷
こちらのSQLを実行すると結果は0件になります。in ('朝食付き','キャンセル無料'))でないもの。なのでその他のタグが相当し、
hotelが全件ヒットするかと思いました。なぜ0件になるのでしょうか。

正直
not exists(
select 1 from hotels_tags b where c.tags_id = b.tags_id and b.hotel_id = a.hotel_id));
の部分も理解できていませんが、まずは質問❶と質問❷についてご回答願います。

Comments

[Orlofsky]

URLは https://teratail.com/help#about-markdown の[リンク]に修正してください。

Answer 1

結論 （長すぎて追記できなくなったため）2018-03-02

not exists を二度使うのはレコードがすべて揃っている場合を検出するため

すべて揃っているのが縦に並ぶレコード（行）だからやりにくいのです。SQLで複数の列の値がすべて not null という条件を調べるのがどれだけ簡単か。また、このために exist を使うなら少なくとも一つレコードがあれば true になり、すべて揃っている場合に含めて余計な場合も検出する。これでは駄目です。

hotel_id	ホテル	タグ
1	「クラウン リージェンシー ホテル & タワーズ」	「朝食付き」<br>「キャンセル無料」
5	「レッド プラネット セブ」	<br>「キャンセル無料」
8	「パーム グラス ホテル」	<br>

前回の質問の回答で、すべて揃っている場合の検出に count(*)=2 を使いましたが、検索条件によってはタグの種類と数が異なり 2 とは限りません。タグの種類が n 種類のとき、あるなしのパターンの数は 2 ** n 個。 n が異なっても、すべてのレコードが揃う場合（一種類だけ）を検出できるのは、not existsを二度使う方法 (二重否定/補集合の補集合) です。 not exists を二度使う方法を初めに考えたひとは尊敬に値すると思いませんか。

関係代数の積、商

この質問のケースでは次の演算を想定してSQLを組みたてています。

積： hotels × tags = hotels_tags
商： hotels_tags ÷ tags = hotels

商の意味は資料を読みましょう。商を求めるSQLは一種類ではありません。

Comments

[sazi]

長文、お疲れ様です。

[退会済みユーザー]

ありがとうございます。

Answer 2

exists/not exists の意味

exists/not exists で条件判定を行います。以下の例(select-1、select-2、select-3、select-4)を使って説明します。

select-1 where exists (select-2)
select-3 where not exists (select-4)

SQLをifを使って書き換えます。この構文は存在しませんがイメージしやすいでしょう。

exists

if exists(select-2の結果、レコードが1件以上存在する(true))
    select-1を実行し結果を得る
else
    空のテーブル

not exists

if not exists(select-4の結果、レコードが1件も存在しない(true))
    select-3を実行し結果を得る
else
    空のテーブル

回答

質問1
SQLを書き換えると以下のようになり 、条件がtrue(レコードが存在する)なので、hotelsテーブルが選択される。

if exists(select 1 from tags c where c.tag in ('朝食付き','キャンセル無料'))
    select * from hotels a
else
    空のテーブル

質問2
SQLを書き換えると以下のようになり 、条件がfalse(レコードが存在する)なので、空のテーブルが選択される。

if not exists(select 1 from tags c where c.tag in ('朝食付き','キャンセル無料'))
    select * from hotels a
else
    空のテーブル

上の例は条件判定が1度しか行われませんが、以下のように相関副問い合わせがある場合は、レコード1件ごとに条件判定が行われます。

not exists(
select 1 from hotels_tags b where c.tags_id = b.tags_id and b.hotel_id = a.hotel_id));

説明資料

SQL全体でやりたいのは、Coddの関係演算（代数）の「商」です。まず資料を読んで考えてください。「商」は「積」の反対演算です。hotels_tags ÷ tgasをやりたいのです。not existsを2回使う意味は、補集合の補集合二重否定をとるためですが、今はわからなくてよいです。

https://ja.wikipedia.org/wiki/%E9%96%A2%E4%BF%82%E4%BB%A3%E6%95%B0_(%E9%96%A2%E4%BF%82%E3%83%A2%E3%83%87%E3%83%AB)

相関副問合せをもつ exists / not exists （ここから、2018-03-02)

くどいかもしれませんが、動作を知りたいという要望に応えます。このあと、まとめとして、関係演算の積、商の解説、さらに、すべてを検索するのになぜexistsを使用するかを解説する予定ですが、そちらで、これについて別の質問をたてたほうがよいかも。(疲れたので休憩します）

相関副問合せにおける exists/not exist の動作

exists/not existの動作を if 構文に直して考えます。外側の検索で読まれたレコード1件ずつを判定の対象とします。

<外側の検索で読まれたレコード> -- <-- 1件が判定の対象
if exists|not exists(<外側の検索で読まれたレコード>の項目の値を使うselectの結果)
    <外側の検索で読まれたレコード>を採用する -- <-- 上の exists/ not existsがtrueの場合
else
    <外側の検索で読まれたレコード>を採用しない

SQL動作確認：（関係演算の除算）ホテルとタグの組がすべて存在するホテルを検索

explain を使ってSQLを調べます。(JSON 形式の出力を行う exlain format=json <SQL> ... もあります。こちらは詳細が表示されます)

> explain select hotel_id from hotels a where not exists(
    ->   select 1 from tags c where c.tag in ('朝食付き','キャンセル無料') and not exists(
    ->     select 1 from hotels_tags b where c.tags_id = b.tags_id and b.hotel_id = a.hotel_id));
+------+--------------------+-------+----------------+---------------+----------+---------+------+------+--------------------------+
| id   | select_type        | table | type           | possible_keys | key      | key_len | ref  | rows | Extra                    |
+------+--------------------+-------+----------------+---------------+----------+---------+------+------+--------------------------+
|    1 | PRIMARY            | a     | index          | NULL          | hotel_id | 6       | NULL |   12 | Using where; Using index |
|    2 | DEPENDENT SUBQUERY | c     | range          | tag           | tag      | 302     | NULL |    2 | Using where; Using index |
|    3 | DEPENDENT SUBQUERY | b     | index_subquery | hotel_id      | hotel_id | 4       | func |    8 | Using index; Using where |
+------+--------------------+-------+----------------+---------------+----------+---------+------+------+--------------------------+
3 rows in set (0.001 sec)

explainからわかるのは select の入れ子です。select_type の意味は次のとおり。

• PRIMARY 主検索
• DEPENDENCY SUBQUERY 外側の検索結果を使用する検索（外側の検索が先に実行される）

上の検索 id.1,id.2,id.3 はすべて「選択」であり「結合」はありません。依存関係は、id.1 <- id.2 <- id.3。以下の疑似コードで動作を示します。

hotels a を全件検索 <-- id.1
  hotelsのレコードごとに以下を実行
    tags c のレコード'朝食付き','キャンセル無料'を検索する。 <-- id.2
        各レコード(2,'朝食付き'),(3,'キャンセル無料')のそれぞれに以下を実行する
          hotels_tags b のレコードを検索する。(<ホテルid>,<タグid=2>)と(<ホテルid>,<タグid=3>)の2回 <-- id.3

ここまでで、SQLの動作の概要を理解してください。

SQL: ホテルごとの動作の確認

ホテルと検索対象のタグの存在有無の組み合わせを調べます。検索で選択されるホテルは、すべてのタグを持つ「クラウン リージェンシー ホテル & タワーズ」だけです。これらの3つのホテルを確認用データとして使います。

hotel_id	ホテル	「朝食付き」(tags_id:2)	「キャンセル無料」(tags_id:3)
1	クラウン リージェンシー ホテル & タワーズ	○	○
5	レッド プラネット セブ	ー	○
8	パーム グラス ホテル	ー	ー

まず、うえのSQLの検索条件に hotel_id を追加、1ホテルごとの検索の和に変形します。

select hotel_id from hotels a where hotel_id = 1 and not exists( ...
union
...
union
select hotel_id from hotels a where hotel_id = 5 and not exists( ...
union
...
union
select hotel_id from hotels a where hotel_id = 8 and not exists( ...
union 
...

確認してください-1
上の個別SQLの hotel_id を、1, 5, 8 のそれぞれで実行して動作を確認してください。

SQL(id.2-1): 必要なタグを検索

タグの検索は全てのホテルで同じです。次の副問合せ(id.3)で使用する tags_id、2, 3 を取得します。すべてのホテルで使用するタグの定数レコードが二件。

select c.tags_id from tags c where c.tag in ('朝食付き','キャンセル無料')

SQL(id.3): ホテル、タグの組の検索

SQL(id.2-1)の各定数( tags_id: 2, 3 )を使って以下の問い合わせを実行します。not existsが二回実行されます。

not exists(select 1 from hotels_tags b where c.tags_id = b.tags_id and b.hotel_id = a.hotel_id)

確認してください-2
hotel_idが、1、 5、 8、tags_idが、2、3 の組み合わせで以下の動作を確認してください。MySQLの結果は、trueが 1、falseが 0 になります。
組み合わせは 6 とおりです。指定する(ホテル、タグ)の組が存在すれば false、存在しなければ trueになります。

select not exists(select 1 from hotels_tags b where <タグid(2,3のいずれか)> = b.tags_id and b.hotel_id = <ホテルのid(1,5,8のいずれか)>);

SQL(id.2-2): ホテル、タグ検索(id.3)の結果をタグ検索にまとめる

各ホテルでtags_id 2,3 の検索を実行したので、UNIONを使って結果をまとめます。select 1 の部分は、わかりやすくするためつぎのように置き換えておきます。

• select '朝食付きなし' from ...
• select 'キャンセル無料なし' from ...

select '朝食付きなし' from tags c where not exists(select 1 from hotels_tags b where 2 = b.tags_id and b.hotel_id = <ホテルのid>) 
union
select 'キャンセル無料なし' from tags c where not exists(select 1 from hotels_tags b where 3 = b.tags_id and b.hotel_id = <ホテルのid>);

確認してください-3
hotel_idが、1、5、8 のそれぞれで、上のまとめ検索SQL(id.2-2)を実行してください。確認するのは次のことです。

• (ホテル、タグ)が存在しなければ、まとめ検索でレコードが存在する。
• (ホテル、タグ)が2つ揃っているなら、まとめ検索のレコードは存在しない。

SQL(id.1-2): ホテル、タグ検索の結果をタグ検索にまとめたものSQL(id.2-2)に not exists を適用する。

最後のホテルの判定処理は、ホテル、タグ検索の結果をタグ検索にまとめたものを使って、ホテルごとになされます。

select hotel_id from hotels a where a.hotel_id = <ホテルのid> and not exists(
select '朝食付きなし' from tags c where not exists(select 1 from hotels_tags b where 2 = b.tags_id and b.hotel_id = <ホテルのid>) 
union
select 'キャンセル無料なし' from tags c where not exists(select 1 from hotels_tags b where 3 = b.tags_id and b.hotel_id = <ホテルのid>)
);

確認してください-4
hotel_idが、1、5、8 のそれぞれで、上のまとめ検索を実行してください。確認するのは次のことです。

• (ホテル、タグ)がひとつでも存在しなければ、ホテルが選択されない。
• (ホテル、タグ)が2つ揃っているときに限りホテルが選択される。

以上が動作の詳細です。プログラマーはこの動作を無意識のうちに（直観的に）考えながらSQLを組み立てています。

確認してください sql 一覧

確認してください-1

select hotel_id from hotels a where hotel_id = 1 and not exists(
  select 1 from tags c where c.tag in ('朝食付き','キャンセル無料') and not exists(
    select 1 from hotels_tags b where c.tags_id = b.tags_id and b.hotel_id = a.hotel_id));
select hotel_id from hotels a where hotel_id = 5 and not exists(
  select 1 from tags c where c.tag in ('朝食付き','キャンセル無料') and not exists(
    select 1 from hotels_tags b where c.tags_id = b.tags_id and b.hotel_id = a.hotel_id));
select hotel_id from hotels a where hotel_id = 8 and not exists(
  select 1 from tags c where c.tag in ('朝食付き','キャンセル無料') and not exists(
    select 1 from hotels_tags b where c.tags_id = b.tags_id and b.hotel_id = a.hotel_id));

確認してください-2

select not exists(select 1 from hotels_tags b where 2 = b.tags_id and b.hotel_id = 1);
select not exists(select 1 from hotels_tags b where 3 = b.tags_id and b.hotel_id = 1);
select not exists(select 1 from hotels_tags b where 2 = b.tags_id and b.hotel_id = 5);
select not exists(select 1 from hotels_tags b where 3 = b.tags_id and b.hotel_id = 5);
select not exists(select 1 from hotels_tags b where 2 = b.tags_id and b.hotel_id = 8);
select not exists(select 1 from hotels_tags b where 3 = b.tags_id and b.hotel_id = 8);

確認してください-3

select '朝食付きなし' from tags c where not exists(select 1 from hotels_tags b where 2 = b.tags_id and b.hotel_id = 1) 
union
select 'キャンセル無料なし' from tags c where not exists(select 1 from hotels_tags b where 3 = b.tags_id and b.hotel_id = 1);
select '朝食付きなし' from tags c where not exists(select 1 from hotels_tags b where 2 = b.tags_id and b.hotel_id = 5) 
union
select 'キャンセル無料なし' from tags c where not exists(select 1 from hotels_tags b where 3 = b.tags_id and b.hotel_id = 5);
select '朝食付きなし' from tags c where not exists(select 1 from hotels_tags b where 2 = b.tags_id and b.hotel_id = 8)
union
select 'キャンセル無料なし' from tags c where not exists(select 1 from hotels_tags b where 3 = b.tags_id and b.hotel_id = 8);

確認してください-4

select hotel_id from hotels a where a.hotel_id = 1 and not exists(
select '朝食付きなし' from tags c where not exists(select 1 from hotels_tags b where 2 = b.tags_id and b.hotel_id = 1) 
union
select 'キャンセル無料なし' from tags c where not exists(select 1 from hotels_tags b where 3 = b.tags_id and b.hotel_id = 1)
);
select hotel_id from hotels a where a.hotel_id = 5 and not exists(
select '朝食付きなし' from tags c where not exists(select 1 from hotels_tags b where 2 = b.tags_id and b.hotel_id = 5) 
union
select 'キャンセル無料なし' from tags c where not exists(select 1 from hotels_tags b where 3 = b.tags_id and b.hotel_id = 5)
);
select hotel_id from hotels a where a.hotel_id = 8 and not exists(
select '朝食付きなし' from tags c where not exists(select 1 from hotels_tags b where 2 = b.tags_id and b.hotel_id = 8) 
union
select 'キャンセル無料なし' from tags c where not exists(select 1 from hotels_tags b where 3 = b.tags_id and b.hotel_id = 8)
);

Comments

[Chandler_Bing]

ありがとうございます。ぼんやりですがなんとなく形は見えてきた気がします。

not exists(
select 1 from hotels_tags b where c.tags_id = b.tags_id and b.hotel_id = a.hotel_id));

の部分は何を行なっているのでしょうか。

[退会済みユーザー]

<あるホテル>1レコードに対して以下の条件がtrueならばそのホテルを採用する、
タグのレコード集合('朝食付き','キャンセル無料')全体に、以下が成立しない
(<あるホテル>,'朝食付き')が存在しない
(<あるホテル>,'キャンセル無料')が存在しない

求める条件は
(<あるホテル>,'朝食付き')が存在し、かつ(<あるホテル>,'キャンセル無料')が存在する(両方存在する)

二重否定は、
((<あるホテル>,'朝食付き')が存在しないか、または(<あるホテル>,'キャンセル無料')が存在しない) が成立しない

(回答者自身もわからくなりそうです)

[Chandler_Bing]

ありがとうございます。もう少しお付き合いください。
hotels_tags b where c.tags_id = b.tags_id and b.hotel_id = a.hotel_id
の部分はテーブル結合でしょうか。

[退会済みユーザー]

結合でなくただの問い合わせ（選択）です。ここで条件に使用する項目の値は、外側の問い合わせで得たレコード項目の値です。具体的には、<あるホテル>のレコード1件にたいして、'朝食付き'タグレコードと'キャンセル無料'タグレコードが順に使われます(ひとつのホテルに二回のselect)。そして次の<あるホテル>のレコード1件にたいして、 ... というふうに続きます。

[退会済みユーザー]

回答の if else 書き換えが不十分であることがわかりました。相関副問い合わせの場合、単純な if else の入れ込構造にならず、while ループの中で if を判定しています。構造が異なるので説明を追加しなければなりません。明日やり直します。

[Chandler_Bing]

ありがとうございます。
もし可能であれば、頂いたSQL文がどのような順番で実行されているのかも知りたいです。

[退会済みユーザー]

動作を逐一解説するより概念を説明するほうが楽です。今回は失敗しました。Coddの8つの関係代数とは？、積は？、商は？という質問のほうが答えやすいですね。補集合の補集合という解説を取り下げたのは、ふと関係代数の差（SQLのMINUS)を使う「商」演算のほうがそれにふさわしいのではないか、と思ったからです。

[Chandler_Bing]

とても丁寧な解説ありがとうございます。まだまだ初心者であると気付かされます。
読解には時間がかかりそうですので、もう少し踏ん張ります。

[退会済みユーザー]

結論を追記しました。結論を読んでわかれば、冗長なSQLは読まなくてもいいです。

[Chandler_Bing]

ありがとうございます。
hotels a を全件検索 <-- id.1
hotelsのレコードごとに以下を実行
tags c のレコード'朝食付き','キャンセル無料'を検索する。 <-- id.2
各レコード(2,'朝食付き'),(3,'キャンセル無料')のそれぞれに以下を実行する
hotels_tags b のレコードを検索する。(<ホテルid>,<タグid=2>)と(<ホテルid>,<タグid=3>)の2回 <-- id.3

(2,'朝食付き'),(3,'キャンセル無料')この部分お2,3はIDなのでしょうか

[退会済みユーザー]

tags_idのつもりです。

Answer 3

解説

SQLは集合を扱う言語です。このSQLは集合理論で言うところの補集合を利用したものです。
NOTによって補集合を取得します。

「複数のtagを全て含まないもの」の補集合が「複数のtagを全て持つもの」となります。

「複数のtagを全て含まないもの」部分のSQLが以下です。

select 1 from tags 
where tag in ('朝食付き','キャンセル無料') 
  and not exists(
        select 1 from hotels_tags
        where tags_id=tags.tags_id
      )

上記では、全て含まないものと、'朝食付き','キャンセル無料'の何れかを含むものが含まれます。

not existsにしているのに、'朝食付き','キャンセル無料'の何れかを含むものが含まれるのはおかしいと思うかもしれませんが、'朝食付き'のみは'キャンセル無料'を含んでいないのでTrueで、'キャンセル無料'のみは'朝食付き'を含んでいないのでこれもTrueとなります。
'朝食付き'または'キャンセル無料'の何れも含むものはFalseとなるのです。
「'朝食付き'と'キャンセル無料'」という集合をhotels_tagsが含まないものという事で、ここが理解する為のポイントです。
その動作は、以下のSQLのhotel_idを変えてみると分かり易いでしょう。

select * from tags
where tag in ('朝食付き','キャンセル無料') 
  and not exists(
        select 1 from hotels_tags
        where tags_id=tags.tags_id and hotel_id=1
      )

上記で「複数のtagを全て含まないもの」が得られるので、それをNotにすることで補集合である「複数のtagを全て持つもの」となります。

追記(質問への回答)

select * from hotels
where exists(
       select 1 from tags where tag in ('朝食付き','キャンセル無料')
      )

質問❶
tagsテーブルにはhotelテーブルと紐付けできるものがないのでこれだと、
inの中にtagsに存在するタグ名(WiFi無料、プール、朝食付きなど)を記入すればtrueとなり
全てを取得するということでしょうか。

hotelsとtagsに相関はありません。
単に、tagに '朝食付き'または'キャンセル無料'というtagがあれば、hotelsは全件抽出されます。

select * from hotels  
where not exists(
       select 1 from tags where tag in ('朝食付き','キャンセル無料')
      )

質問❷
こちらのSQLを実行すると結果は0件になります。in ('朝食付き','キャンセル無料'))でないもの。
なのでその他のタグが相当し、hotelが全件ヒットするかと思いました。
なぜ0件になるのでしょうか。

こちらは質問①の補集合ですね。

---

因みに配列の扱えるDBMSだと補集合とするような事をしなくても、非常に簡潔で高速な問合せが可能です。
postgres でhotelsにtags int[] でtagを配列で持つ場合、以下で同じ結果が得られます。

select * from hotels where tags @> array[2,3]::int[]

hotels_tagsと同内容を得る場合は、unnestで配列を行に展開します。

select hotel_id, unnest(tags) as tags_id from hotels

DBMSの選定も重要な要素だと思いませんか。

Comments

[Chandler_Bing]

ありがとうございます。全体集合は何に当たるのでしょうか。

[sazi]

ここでの全体集合というのならhotels_tagsとtagsの組合せ全てだと思います。

[sazi]

因みに、前回質問で両方を試されたんですよね。
どの程度の件数で、それぞれがどの程度のパフォーマンスだったか、もし、よければ教えて下さい。