Tech Racho エンジニアの「？」を「！」に。
  1. TOP
  2. 開発
  3. SQLで全称量化を表現する方法 & NULLと付き合う上での注意点
  • 開発

SQLで全称量化を表現する方法 & NULLと付き合う上での注意点

Web開発部のkonagaです。

最近、業務でSQLの書き方に悩むことが多く、特に全称量化をどう表現するか試行錯誤していました。
SQLに慣れている方にとっては今更な話かもしれませんが、全称量化の表現方法とNULLに関する注意点について調べたのでまとめていこうと思います。

※検証時には手元にあったPostgreSQL9.4を利用しています。RDBMSの種類やバージョン次第では正しく動作しない可能性があることをご了承ください。

はじめに：全称量化子について

「全てのxが条件Pを満たす」ことを表現する記号のことを全称量化子といいます。
この記号は、allの頭文字Aを反転させた∀で表します。

……これだけだとよく分からないので、以下のような例を挙げてみます。

  • 生徒全員がテストで60点以上を獲得しているクラス
  • 全ての工程が完了しているプロジェクト

全称量化子によって、このようなクラスやプロジェクトを選択することができます。

ちなみに「条件Pを満たすxが存在する」ことを表現する記号も存在していて、これは存在量化子といいます。
この記号は、existの頭文字Eを反転させた∃で表します。

テーブルを設けた例

もうすこし話を進めるため、とある飲食店経営企業が存在し、店舗ごとのメニューとその値段を管理するmenusテーブルが用意されているとします。
実際にはテーブルがもっと正規化されると思いますし、別店舗の同じ商品でこんなに値段が違うのも現実的ではないと思います。
あくまで例に過ぎない、ということでご容赦ください。

shop_name drink price
A店 タピオカミルクティー 400
A店 タピオカ抹茶ミルクティー 450
A店 タピオカ豆乳 500
B店 タピオカミルクティー 550
B店 タピオカ抹茶ミルクティー 600
B店 タピオカジャスミンティー 600
C店 タピオカミルクティー 450
C店 タピオカラテ 500
C店 タピオカマンゴーミルクティー 550

このテーブルから「全てのドリンクが500円以下の店舗名」を検索すると、A店だけが返ってきます。
一部のドリンクが500円より高いC店は返ってきません。

これが「500円以下のドリンクが存在する店舗名」という検索内容だと、C店も返ってくるという違いが生じます。

SQLで全称量化を表現する

では「全てのドリンクが500円以下の店舗名」をSQLで表現していきたいと思います。
残念ながら、SQLには全称量化子に直接対応する述語が存在しません。
しかし、SQLの様々な構文を利用して、全称量化の表現をSQLに落とし込むことは可能です。

1. 対偶を取ってからNOT EXISTS述語を使う

「全てのxが条件Pを満たす」という表現は、「条件Pを満たさないxが存在しない」という対偶と同値です。
「〜が存在しない」という表現についてはNOT EXISTS述語が対応するため、対偶を取ることでSQLに書き下せそうですね。

具体的な検索内容を二重否定で言い換えてみます。

「全てのドリンクが500円以下の店舗名」　→　「500円より高いドリンクが存在しない店舗名」

この表現に対応するSQLは以下のように記述します。

SELECT DISTINCT shop_name
  FROM menus m1
 WHERE NOT EXISTS
       (SELECT *
          FROM menus m2
         WHERE m2.price > 500
           AND m2.shop_name = m1.shop_name);

このSQLは以下のような結果を返します。期待通りですね。

shop_name
A店

NOT EXISTSを使ったSQLには二重否定特有の読みにくさが生じます。
一方で、店舗名の列にインデックスがある場合には利用してくれることがある、というメリットもあります。
クエリの実行速度を追求するなら選択肢に入る表現だと思います。

2. ALL述語を使う

次はALL述語を使って全称量化を行っていきます。
ALL述語は各条件をANDで連結して評価します。

SELECT DISTINCT shop_name
  FROM menus m1
 WHERE 500
        >= ALL (SELECT price
                  FROM menus m2
                 WHERE m2.shop_name = m1.shop_name);

例えば、A店に限って言えばWHERE句は以下のように展開できます。

WHERE (500 >= 400) AND (500 >= 450) AND (500 >= 500)
-- -> TRUE

こうして各店舗について500 >= ALL(...)を評価した結果、唯一TRUEと評価されたA店が検索結果として返ってきます。

3. HAVING句 + COUNT関数を使う

HAVING句の表現力は非常に強く、全称量化にも利用することができます。
私は今まで深く考えずにNOT EXISTSを使っていればいいと思っていたのですが、記事執筆中にHAVINGの方が人間に優しいなと思い直しました。

SELECT shop_name
  FROM menus
 GROUP BY shop_name
HAVING COUNT(*) = SUM(CASE WHEN price <= 500 THEN 1 ELSE 0 END);

まずCASE式ですが、これは値段が500円以下であるかどうかというフラグを付けているようなものです。

shop_name drink price flag
A店 タピオカミルクティー 400 1
A店 タピオカ抹茶ミルクティー 450 1
A店 タピオカ豆乳 500 1
B店 タピオカミルクティー 550 0
B店 タピオカ抹茶ミルクティー 600 0
B店 タピオカジャスミンティー 600 0
C店 タピオカミルクティー 450 1
C店 タピオカラテ 500 1
C店 タピオカマンゴーミルクティー 550 0

ここでSELECTするべきなのは「ドリンクのflagが全て1になっている」店舗名です。
この条件は「店舗ごとのドリンクの総数 = 店舗ごとのflagの総和」に置き換えることができます。
さらに「店舗ごとのドリンクの総数」はCOUNTで行数をカウントすることで求められます。
最後にHAVINGで条件をまとめれば上のようなSQLが完成します。

追記(2019/08/23)

コメントで指摘をいただきましたが、後述するMAX関数をHAVING句と合わせて利用すれば、CASE式よりもさらに分かりやすく記述することができます。

SELECT shop_name
  FROM menus
 GROUP BY shop_name
HAVING MAX(price) <= 500;

ただしMAXにはALLやCOUNT(*)と違い、NULLを除外するという特徴があります。後ほど説明します。

SQL表現についてのまとめ

以上、三通りの表現方法を紹介しました。
私の印象はこんな感じです。

  1. NOT EXISTS: 速くなることがあるとはいえ、二重否定のせいで可読性が低い
  2. ALL: 可読性はまずまずだがサブクエリがやや長い
  3. HAVING + COUNT: 簡潔に書けて可読性が高い

あくまで個人的な意見ですが、特別な考慮が必要なければHAVINGを使うのが良さそうだと思っています。

NULLと付き合う上での注意点

全称量化の表現には、テーブルにNULLが存在する場合に異なる結果を返すものが存在します。
その違いについて確認していきましょう。

1. 極値関数とALL述語の違い

結論から

極値関数とALL述語はそれぞれ全称量化に利用できますが、極値関数がNULLを除外する一方で、ALL述語はNULLを除外しません。

では説明していきます。

極値関数MAXによる全称量化

先ほどALL述語を利用して「全てのドリンク」について値段が500円以下であるか確認しましたが、そのうちの一つだけに着目して次のように言い換えることもできます。

「全てのドリンクが500円以下の店舗名」　→　「全てのドリンクのうち、最も高いドリンクが500円以下の店舗名」

これを表すには、極値関数のMAXを利用します。

SELECT DISTINCT shop_name
  FROM menus m1
 WHERE 500
        >= (SELECT MAX(price)
              FROM menus m2
             WHERE m2.shop_name = m1.shop_name);
shop_name
A店

こうしてみると、MAXでもALL述語と同じような全称量化が可能であるように思えます。
しかし、極値関数にはNULLを除外するという特徴があります。

極値関数とNULL

この特徴を確認するために、A店で販売されているドリンクのpriceを一部NULLにしてみましょう。

shop_name drink price
A店 タピオカミルクティー 400
A店 タピオカ抹茶ミルクティー 450
A店 タピオカ豆乳 NULL
B店 タピオカミルクティー 550
B店 タピオカ抹茶ミルクティー 600
B店 タピオカジャスミンティー 600
C店 タピオカミルクティー 450
C店 タピオカラテ 500
C店 タピオカマンゴーミルクティー 550

MAXは引数からNULLを除外するため、500 >= NULLという比較がされてUNKNOWNと評価されることはありません。
（>=でNULLと比較した場合、結果はUNKNOWNになります）

WHERE 500
       >= (SELECT MAX(price)
             FROM menus m2
            WHERE m2.shop_name = m1.shop_name);

-- A店についてサブクエリを評価 
WHERE 500 >= 450

検索結果は変わらずA店のみです。

shop_name
A店

ALL述語とNULL

一方、ALLを利用したSQLを実行すると、検索結果は空になり、A店が選択されません。

shop_name

これはWHERE句が「UNKNOWNまたはFALSE」としか評価されないためです。
A店についてALLを展開すると、500 >= NULLが含まれるため、UNKNOWNと評価されることがわかります。

WHERE 500
       >= ALL (SELECT price
                 FROM menus m2
                WHERE m2.shop_name = m1.shop_name);

-- ALLを展開する 
WHERE (500 >= 400) AND (500 >= 450) AND (500 >= NULL) 
-- -> UNKNOWN

どちらの表現を使うべきか

ALL述語と極値関数ではどちらを使うのが適切なのか、という話ですが、これは状況によると思います。
SQLの筋としてどちらが正しいかというよりも、どちらの方が検索したい条件に合うかで選べば良さそうです。

もう一度テーブルを確認してみましょう。

shop_name drink price
A店 タピオカミルクティー 400
A店 タピオカ抹茶ミルクティー 450
A店 タピオカ豆乳 NULL

まず、今回のNULLは「タピオカ豆乳は販売されているが、その値段がいくらになったのか判明していない」という意味で設定されていると考えることができます。
実際の値段は500円かもしれないし700円かもしれません。500円以下であるかは判断ができません。
そして判断ができない以上、タピオカ豆乳を販売しているA店を「全てのドリンクが500円以下の店舗名」と言うことはできません。
この考え方だと、UNKNOWNを含めるALL述語を使う方が適切な結果が得られます。

一方で、「タピオカ豆乳は販売前の新メニューなのでpriceがNULLになっている」「店頭に出ていないので『全てのドリンクが500円以下の店舗名』を探すときには無視して良い」という考え方（というよりはデータ管理の仕方）もあるかもしれません。
この場合はNULLを無視することになります。
表現としては極値関数MAXを使うこともできるのですが、それよりもWHERE句でpriceがNULLの行を弾いた方が意味が分かりやすく、親切ではないかと思っています。
好みの範囲といえばそれまでかもしれませんが。

SELECT DISTINCT shop_name
  FROM menus m1
 WHERE 500
        >= ALL (SELECT price
                  FROM menus m2
                 WHERE m2.shop_name = m1.shop_name
                   AND m2.price IS NOT NULL);

書き方に関わらず、NULLの存在によってALLと極値関数が同じ結果を返さなくなることは、把握しておいて損はないと思います。

2. COUNT(*)とCOUNT(列名)の違い

結論から

COUNT関数には以下の二種類があり、カウントする対象が異なります。

  • COUNT(*) : 全行を数える
  • COUNT(列名) : ある列からNULLを除外した行を数える

……既視感がありますね？全称量化の文脈における二つのCOUNT関数は、先ほどの「極値関数とALL述語の違い」と同様にNULLを除外するかどうかが異なります。

COUNT(*)とNULL

再びA店のドリンクのpriceを一部NULLにしておきます。

shop_name drink price
A店 タピオカミルクティー 400
A店 タピオカ抹茶ミルクティー 450
A店 タピオカ豆乳 NULL
B店 タピオカミルクティー 550
B店 タピオカ抹茶ミルクティー 600
B店 タピオカジャスミンティー 600
C店 タピオカミルクティー 450
C店 タピオカラテ 500
C店 タピオカマンゴーミルクティー 550

COUNT(*)を利用したSQLの場合は、検索結果は空になり、A店が選択されません。

SELECT shop_name
  FROM menus
 GROUP BY shop_name
HAVING COUNT(*) = SUM(CASE WHEN price <= 500 THEN 1 ELSE 0 END);

HAVING句をA店に限って展開し、どのような式が成立しているのか確認してみましょう。

-- COUNT(*)の場合のSQL
HAVING 3 = SUM((CASE WHEN 400 <= 500 THEN 1 ELSE 0 END), 
                (CASE WHEN 400 <= 450 THEN 1 ELSE 0 END), 
                (CASE WHEN 400 <= NULL THEN 1 ELSE 0 END));

-- WHENの不等式を評価
HAVING 3 = SUM((CASE WHEN (TRUE) THEN 1 ELSE 0 END), 
                (CASE WHEN (TRUE) THEN 1 ELSE 0 END), 
                (CASE WHEN (UNKNOWN) THEN 1 ELSE 0 END));

-- CASE式を評価する
HAVING 3 = SUM(1, 1, 0);

最後には3 = 2となるため、HAVING句はFALSEと評価されます。

COUNT(列名)とNULL

一方、COUNT(price)を利用したSQLの場合は、A店が検索結果に含まれます。
これはpriceがNULLとなっている行がカウントされないためです。

-- COUNT(price)の場合のSQL
SELECT shop_name
  FROM menus
 GROUP BY shop_name
HAVING COUNT(price) = SUM(CASE WHEN price <= 500 THEN 1 ELSE 0 END);

-- COUNT関数とCASE式を評価する
HAVING 2 = SUM(1, 1, 0);

HAVING句はTRUEと評価されます。

この違いも知っておくと、あえてNULLを無視したい場合にはCOUNT(列名)が使えるかと思います。
分かりやすいかは微妙なところですが、オシャレな書き方ですね。

おわりに

SQLの全称量化表現についてはあまり情報を見かけないので整理してみました。
興味を持った方は、参考文献の方が精度が高く内容が充実しているため、そちらを読むことをおすすめします。

参考文献

おたより発掘


タグ
この記事を書いた人

konaga

元SEの現Railsエンジニアです

konagaの書いた記事一覧へ

本記事の内容へのお問い合せはTwitterで@techrachoへMentionまたはDMにてご連絡頂くか、運営会社であるBPS株式会社のお問い合せフォームよりお問い合せ下さい。

CONTACT

TechRachoでは、パートナーシップをご検討いただける方からの
ご連絡をお待ちしております。ぜひお気軽にご意見・ご相談ください。
CONTACT FORM