SQL の相関サブクエリ (2) – EXISTS 述語

「SQL の相関サブクエリ – filter 関数から考える」 のつづき

前回、IN 述語と共に使われた相関サブクエリを、関数のネストとの比較を通して動作のイメージを考えた。今回はIN 述語以外の述語で相関サブクエリを使ってみる。

 

EXISTS 述語

まずは代表的な EXISTS 述語。

プログラマのためのSQL 第2版 の第15章 EXISTS 述語 (p189) によると、

EXISTS 述語の意味はごく自然です。これは、空でない集合の検査です。もし、そのサブクエリーが何らかの行を返せば、結果は TRUE です。そして、さもなければ FALSE になります。 UNKNOWN が結果として返ることはありません。…

(太字は引用者による)

 

対象のデータベース

前回と同じ以下のテーブルで試す。

データの内容は少し変更した。

オブジェクト図で示すと、

 

サブクエリが必ず行を返す場合

初めに確認しておくことは、サブクエリがダミーのテーブルを参照し、必ずクエリが行を返す場合。

select *
from persons 
where exists (select 0 as hoge from dual)

サブクエリの結果が必ず存在するので、各要素で exists 述語が True となり、全ての 「人」 が抽出される。

 

相関サブクエリを利用する場合

次に、

「グループ」 に 「割当て」 られた 「人」

を EXISTS 述語を使って抽出してみる。

オブジェクト図で見た場合、「人」 から 「割当て」 へのリンクが存在する人を選び出す。

SQL を書くときに意識することは、

1. 抽出したいのは、とある 「人」 なんだけれど、
2. その人は、「グループ」 に 「割当て」 られている。

という順序。

前回と同じように WHERE 句は集合の要素に対する述語であることを思い出しながら書く。

select * 
from persons as p
where exists (select *
              from assignments as a
              where p.id = a.p_id)

結果は、

+----+--------+--------+-----+
| id | name   | gender | age |
+----+--------+--------+-----+
|  1 | Tarou  |      1 |  10 |
|  2 | Hanako |      2 |  20 |
|  3 | Jirou  |      1 |  30 |
+----+--------+--------+-----+
3 rows in set (0.00 sec)

上記のサブクエリにおける SELECT 句で `*’ が書かれているのは、

SQL-89 の規則では、サブクエリーは 1 つのカラムか、 * を使った SELECT 句であるとなっています。もし、 SELECT * オプションが使われると、データベースエンジンは、 1 つのカラムを選択し、(理論上は) それを使います。

(プログラマのためのSQL 第2版 の第15章 EXISTS 述語, p189 より)

 

相関サブクエリを利用しない場合

ちなみに相関サブクエリを使わない場合は、

1. 先に 「割当て」 に注目し、
2. 「割当て」 に結合する 「人」が
3. 重複しないように抽出

すればいいので、

select distinct p.*
from assignments as a 
     join persons as p on a.p_id = p.id

 

Haskell で類似したコードを考える

上記に類似したコードを Haskell で考える。

予め SQL の exists 述語に相当する exists 関数を定義しておき、

exists = not . null

これを利用して次のように書く。

filter (\p -> exists
        (filter (\a -> a_p_id a == p_id p)
                assignments))
       persons

( cf. gist: 645292 - GitHub )

リスト内包表記なら、

[p | p <- persons
   , exists [a | a <- assignments
               , a_p_id a == p_id p]]

( cf. gist: 645292 - GitHub )

「SQL の相関サブクエリ (3) - 量化された比較述語 ALL, ANY」につづく