EXISTSでIS NULLを使う時の処理

Question

お世話になっております。
PostgreSQLを使っています。
以下のようにEXISTSでhogeinの中身がNULLの時trueの値を取得したいのですが実際にＳＱＬで出力してみるとdel_flg = 0しか効いていないような状態になってしまいます。

SELECT * FROM hoge WHERE del_flg = 0 AND EXISTS(SELECT 1 FROM hoge WHERE hogein IS NULL)

以下のようにEXISTSを使わないと効くのでEXISTSに関してなにか勘違いをしているのかな…と思いつつ検討がつかない状態です。

SELECT * FROM hoge WHERE del_flg = 0 AND hogein IS NULL

よろしくお願いします。

Answer 1

SELECT * FROM hoge WHERE del_flg = 0 AND hogein IS NULL

これと等価なことをやりたければ、次のような相関サブクエリにする必要があります。

仮に hoge テーブルの主キーが id だとすると次のようになります。

SELECT * FROM hoge as a WHERE del_flg = 0 AND EXISTS(
    SELECT 1 FROM hoge as z WHERE z.id = a.id AND z.hogein IS NULL
)

これは、外側のクエリの１行ごとに、内側のクエリの a.id を外側のクエリの a.id の値に置き換えつつ内側のクエリを実行し、その結果が空でなければ EXISTS の部分は true と評価される、という感じの動作になります。

このような、サブクエリの内側に、外側のクエリの結果を使用するサブクエリを、相関サブクエリと呼びます。

---

なお、質問に書かれているクエリの場合、下記の部分は、

EXISTS(SELECT 1 FROM hoge WHERE hogein IS NULL)

次のクエリを実行した結果が空でなければ、

SELECT 1 FROM hoge WHERE hogein IS NULL

という意味になります。したがって hoge テーブルに hogein IS NULL である行が１行でもあれば、常に true になるので「del_flg = 0しか効いていないような状態」になります。

---

ちなみに、やりたいことの結果が下記と同じなのであれば、

SELECT * FROM hoge WHERE del_flg = 0 AND hogein IS NULL

わざわざ相関サブクエリを使ってやるひつようはないと思いますが・・？

Answer 2

SELECT * FROM hoge WHERE del_flg = 0 AND EXISTS(SELECT 1 FROM hoge WHERE hogein IS NULL) 

は「hogeテーブルの中に一つでも hogeinがNULLなレコードが存在していたら、 del_flgが0のレコードを全て取ってくる」という意味になります。
ですので、勘違いだと思います。