NULLと付き合う上での注意点|TechRacho(テックラッチョ)〜エンジニアの「?」を「!」に〜|BPS株式会社エンジニアの「?」を「!」に。NULLと付き合う上での注意点konagaシェアツイートブックマークLINEWeb開発部のkonagaです。最近、業務でSQLの書き方に悩むことが多く、特に全称量化をどう表現するか試行錯誤していました。SQLに慣れている方にとっては今更な話かもしれませんが、全称量化の表現方法とNULLに関する注意点について調べたのでまとめていこうと思います。※検証時には手元にあったPostgreSQL9.4を利用しています。RDBMSの種類やバージョン次第では正しく動作しない可能性があることをご了承ください。はじめに:全称量化子について「全てのxが条件Pを満たす」ことを表現する記号のことを全称量化子といいます。この記号は、allの頭文字Aを反転させた∀で表します。......これだけだとよく分からないので、以下のような例を挙げてみます。生徒全員がテストで60点以上を獲得しているクラス全ての工程が完了しているプロジェクト全称量化子によって、このようなクラスやプロジェクトを選択することができます。ちなみに「条件Pを満たすxが存在する」ことを表現する記号も存在していて、これは存在量化子といいます。この記号は、existの頭文字Eを反転させた∃で表します。テーブルを設けた例もうすこし話を進めるため、とある飲食店経営企業が存在し、店舗ごとのメニューとその値段を管理するmenusテーブルが用意されているとします。実際にはテーブルがもっと正規化されると思いますし、別店舗の同じ商品でこんなに値段が違うのも現実的ではないと思います。例に過ぎない、ということでご容赦ください。550このテーブルから「全てのドリンクが500円以下の店舗名」を検索すると、A店だけが返ってきます。一部のドリンクが500円より高いC店は返ってきません。これが「500円以下のドリンクが存在する店舗名」という検索内容だと、C店も返ってくるという違いが生じます。SQLで全称量化を表現するでは「全てのドリンクが500円以下の店舗名」をSQLで表現していきたいと思います。残念ながら、SQLには全称量化子に直接対応する述語が存在しません。SQLの様々な構文を利用して、全称量化の表現をSQLに落とし込むことは可能です。EXISTS述語を使う「全てのxが条件Pを満たす」という表現は、「条件Pを満たさないxが存在しない」という対偶と同値です。EXISTS述語が対応するため、対偶を取ることでSQLに書き下せそうですね。具体的な検索内容を二重否定で言い換えてみます。「500円より高いドリンクが存在しない店舗名」この表現に対応するSQLは以下のように記述します。m1.shop_name);このSQLは以下のような結果を返します。EXISTSを使ったSQLには二重否定特有の読みにくさが生じます。一方で、店舗名の列にインデックスがある場合には利用してくれることがある、というメリットもあります。クエリの実行速度を追求するなら選択肢に入る表現だと思います。ALL述語を使う次はALL述語を使って全称量化を行っていきます。ALL述語は各条件をANDで連結して評価します。m1.shop_name);例えば、A店に限って言えばWHERE句は以下のように展開できます。ALL(...)を評価した結果、唯一TRUEと評価されたA店が検索結果として返ってきます。COUNT関数を使うHAVING句の表現力は非常に強く、全称量化にも利用することができます。EXISTSを使っていればいいと思っていたのですが、記事執筆中にHAVINGの方が人間に優しいなと思い直しました。END);まずCASE式ですが、これは値段が500円以下であるかどうかというフラグを付けているようなものです。0ここでSELECTするべきなのは「ドリンクのflagが全て1になっている」店舗名です。店舗ごとのflagの総和」に置き換えることができます。「店舗ごとのドリンクの総数」はCOUNTで行数をカウントすることで求められます。最後にHAVINGで条件をまとめれば上のようなSQLが完成します。追記(2019/08/23)コメントで指摘をいただきましたが、後述するMAX関数をHAVING句と合わせて利用すれば、CASE式よりもさらに分かりやすく記述することができます。500;ただしMAXにはALLやCOUNT(*)と違い、NULLを除外するという特徴があります。SQL表現についてのまとめ以上、三通りの表現方法を紹介しました。私の印象はこんな感じです。簡潔に書けて可読性が高いあくまで個人的な意見ですが、特別な考慮が必要なければHAVINGを使うのが良さそうだと思っています。全称量化の表現には、テーブルにNULLが存在する場合に異なる結果を返すものが存在します。その違いについて確認していきましょう。極値関数とALL述語の違い結論から極値関数とALL述語はそれぞれ全称量化に利用できますが、極値関数がNULLを除外する一方で、ALL述語はNULLを除外しません。先ほどALL述語を利用して「全てのドリンク」について値段が500円以下であるか確認しましたが、そのうちの一つだけに着目して次のように言い換えることもできます。これを表すには、極値関数のMAXを利用します。m1.shop_name);shop_nameA店こうしてみると、MAXでもALL述語と同じような全称量化が可能であるように思えます。極値関数にはNULLを除外するという特徴があります。極値関数とNULLこの特徴を確認するために、A店で販売されているドリンクのpriceを一部NULLにしてみましょう。NULLという比較がされてUNKNOWNと評価されることはありません。検索結果は変わらずA店のみです。shop_nameA店ALL述語とNULL一方、ALLを利用したSQLを実行すると、検索結果は空になり、A店が選択されません。shop_nameこれはWHERE句が「UNKNOWNまたはFALSE」としか評価されないためです。NULLが含まれるため、UNKNOWNと評価されることがわかります。UNKNOWNどちらの表現を使うべきかALL述語と極値関数ではどちらを使うのが適切なのか、という話ですが、これは状況によると思います。SQLの筋としてどちらが正しいかというよりも、どちらの方が検索したい条件に合うかで選べば良さそうです。一度テーブルを確認してみましょう。NULLまず、今回のNULLは「タピオカ豆乳は販売されているが、その値段がいくらになったのか判明していない」という意味で設定されていると考えることができます。実際の値段は500円かもしれないし700円かもしれません。500円以下であるかは判断ができません。判断ができない以上、タピオカ豆乳を販売しているA店を「全てのドリンクが500円以下の店舗名」と言うことはできません。この考え方だと、UNKNOWNを含めるALL述語を使う方が適切な結果が得られます。一方で、「タピオカ豆乳は販売前の新メニューなのでpriceがNULLになっている」「店頭に出ていないので『全てのドリンクが500円以下の店舗名』を探すときには無視して良い」という考え方(というよりはデータ管理の仕方)もあるかもしれません。この場合はNULLを無視することになります。表現としては極値関数MAXを使うこともできるのですが、それよりもWHERE句でpriceがNULLの行を弾いた方が意味が分かりやすく、親切ではないかと思っています。好みの範囲といえばそれまでかもしれませんが。NULL);書き方に関わらず、NULLの存在によってALLと極値関数が同じ結果を返さなくなることは、把握しておいて損はないと思います。COUNT(*)とCOUNT(列名)の違い結論からCOUNT関数には以下の二種類があり、カウントする対象が異なります。ある列からNULLを除外した行を数える......既視感がありますね?全称量化の文脈における二つのCOUNT関数は、先ほどの「極値関数とALL述語の違い」と同様にNULLを除外するかどうかが異なります。COUNT(*)とNULL再びA店のドリンクのpriceを一部NULLにしておきます。550COUNT(*)を利用したSQLの場合は、検索結果は空になり、A店が選択されません。END);HAVING句をA店に限って展開し、どのような式が成立しているのか確認してみましょう。2となるため、HAVING句はFALSEと評価されます。COUNT(列名)とNULL一方、COUNT(price)を利用したSQLの場合は、A店が検索結果に含まれます。これはpriceがNULLとなっている行がカウントされないためです。0);HAVING句はTRUEと評価されます。この違いも知っておくと、あえてNULLを無視したい場合にはCOUNT(列名)が使えるかと思います。分かりやすいかは微妙なところですが、オシャレな書き方ですね。おわりにSQLの全称量化表現についてはあまり情報を見かけないので整理してみました。興味を持った方は、参考文献の方が精度が高く内容が充実しているため、そちらを読むことをおすすめします。2019シェアツイートブックマークLINEこの記事を書いた人konaga2019年3月入社。元SE、現Railsエンジニアです。Storageを使ってはいけない(翻訳)#Tags#会社紹介#自社サービス#勉強会#Rails#PHP#デザイン#実績#週刊RailsウオッチCONTACTお気軽にご相談ください。(翻訳)CONTACTTechRachoでは、パートナーシップをご検討いただける方からのご連絡をお待ちしております。お気軽にご意見・ご相談ください。