D.Horiyama's output

達人に学ぶSQL徹底指南書 第2版 Chapter4

SQL勉強メモ

出典: 

4. 3値論理とNULL

序文

  • true/false/unknown
  • nullが含まれたデータベースから正しい結果が得られることは確信できない

本題に入る前に

  • なぜt/f/uの3値論理なのか

    • NULLを持ち込んだことで、そうせざるを得なくなった

理論編

NULLについて

  • 3値論理のすべての元凶

NULLを2つに分類 by E.F.コッド

  • 2つのNULL

    • 未知(UNKNOWN)

      • 調べればわかる系のやつ

    • 適用不能(N/A: Not Applicable)

      • 「無意味」「論理的に不可能」「定義不能」という感じのやつ

  • これらを厳密に区別しようとすると、4値論理になる

    • 真理値表がえらいことになる
  • 分類自体は有用

2つのNULLをひとまとめに

  • 3値論理になる

    • ‘18/12/03現在、すべてのDBMSはそうしている

      • 世界は救われた

なぜ「= NULL」ではなく「IS NULL」と書かなければならないのか？

  • NULLに比較述語を適用すると、すべてunknownになるから

    • NULLは値ではないので、比較述語を適用すること自体が無意味

      • 「値がありません」という印にすぎない

  • IS NULL で1述語ととらえるべき

    • JavaScriptのisNaNと似ているかも
      JSでも、 NaN === NaN はfalseを返してしまうので、
      専用の述語Number.isNaN(NaN); // trueを使う必要がある

unknown、第三の真理値

注意

  • これらは異なるもの

    • 3値論理

      • true
      • false
      • unknown
      • れっきとした「値」

    • 2つのNULL

      • UNKNOWN
      • NULLなので、「値でも変数でもない」
      • N/A

真理値表

優先順位

  • AND

    • false > unknown > true

  • OR

    • true > unknown > false
まじめにみてみる

unknownは、trueなのか、falseなのかがわからない

例: true AND unknown

true AND true -- true

なのか

true AND false -- false

なのかがわからないので、unknown

例: false AND unknown

false AND true -- false

なのか

false AND false -- false

なのかがわからないが、どちらにせよfalse

問題

  • a = 2
  • b = 5
  • c = NULL
  • a < b AND b > c = true AND unknown = unknown
  • a > b OR b < c = false OR unknown = unknown
  • a < b OR b < c = true OR unknown = true
  • NOT (b <> c) = NOT unknown = unknown

実践編

1.比較述語とNULL その1 — 排中律が成立しない

https://sqliteonline.com/#fiddle-5c06e61908ec1asdjpa7j95v

排中律(excluded middle)

「命題とその否定をまたはでつなげてできる命題はすべて真である」

2値論理の世界でないと成立しない

3値論理で「すべて真である命題」をつくるには

SELECT * 
   FROM Students
  WHERE age = 20    -- 20歳である
     OR age <> 20   -- 20歳でない
------------------------------ 2値論理の世界ではここまでで十分
     OR age is NULL -- 年齢はわからないOR定義できない
;

対処

IS [NOT] DISTINCT FROM

NULLを「他のどの値とも一致しない値」として扱える

WHERE A IS NOT DISTINCT FROM B

(訳: AはBと異なりません)

は下記と同じ

WHERE (A = B OR (A IS NULL AND B IS NULL))

  • 式A, BがいずれもNULLでなく等しい場合、当然true
  • 式A,Bの一方のみがNULLの場合、false
  • 式A, Bの両方がNULLの場合、true

標準SQLだが、サポートはまだ広くない

  • PostgreSQL
  • Firebird

sqliteではだめでした

DBMS独自実装
  • Oracle
    LNNVL
  • MySQL
    <=>

2.比較述語とNULL その2 — CASE式とNULL

単純CASE式使うな

CASE col_1
  WHEN 1    THEN 'o'
  WHEN NULL THEN 'x' -- 絶対ここに来ない
END

こう書いてるのと同じ

CASE
  WHEN col_1 = 1    THEN 'o'
  WHEN col_1 = NULL THEN 'x' -- NULLに比較述語:unknownに評価される
END

こうしないとだめ

CASE
  WHEN col_1  = 1    THEN 'o'
  WHEN col_1 IS NULL THEN 'x' -- 意図した動作をする
END

3.NOT IN と NOT EXISTSは同値でない

サンプル

INをEXISTSで置き換えることは、パフォーマンスチューニングのテクニックとしてよく行われる。これはOK

NOT INをNOT EXISTSで置き換えると必ずしも結果が一致しない

NOT INのサブクエリの結果にNULLが含まれると
falseまたはunknownにしか評価されなくなり、SQL全体として結果は常に空になる

NOT EXISTSはOK

INとEXISTSとなにがちがうの

  • IN
    ORと=で同値変換される
    =比較述語が絡む以上、unknownに評価されうる
  • EXISTS
    それ以上同値変換されず、また、trueかfalseにしか評価されない

4.限定述語とNULL

sqliteでALL限定述語を使えなかったのでこちら

paiza.IO

限定述語

関数型プログラミングおなじみ

  • ANY

    • 式 comp ANY (サブクエリ)
      サブクエリの結果すべてに比較述語comp(<とか)を作用させ、
      どれか一つでも(any)trueならtrueに評価される

  • ALL

    • 式 comp ALL (サブクエリ)
      サブクエリの結果すべてに比較述語comp(<とか)を作用させ、
      すべて(all)trueならtrueに評価される

NULLが絡んだ時の挙動

compによる比較がOR/ANDで展開されるため、NULLが絡むとunknownに評価されうる

ALLの対象にNULLが含まれると、unknownを含むANDの演算に展開され、trueに評価されなくなる

5.限定述語と極値関数は同値ではない

サンプル

  • 「彼は東京在住の生徒の誰よりも(ALL)若い」
  • 「彼は東京在住の最も若い(MIN)生徒よりも若い(MIN)」

極値関数の特徴

  • NULLを集計に含めない
  • 集計対象が空集合の場合、NULLを返す

まとめ

限定述語 極値関数
例 ALL MIN
評価結果データ型 論理値型 集計対象データ型
対象にNULLが含まれると 演算対象。
unknownを含むAND/OR演算に同値変換される		 排除
対象が空集合だと 限定述語による NULLを返す
限定述語にて、対象が空集合の場合
  • ALL
    trueに評価される => 全行返される
  • ANY
    falseに評価される => 1行も返されない

たぶんこうなってるんでしょうね

ALL a < bs
↓
true AND (a < b1) AND (a < b2) AND ...

bsが空だとtrue (AND演算で一番弱いやつ)
ANY a < bs
↓
false OR (a < b1) OR (a < b2) OR ...

bsが空だとfalse (OR演算で一番弱いやつ)
極値関数にNULLを返されると困る場合

COALESCE (コゥア↓レス↑)関数でNULLを非NULLの値に読み替える

  • 与えられた引数のうち、最初に見つかった非NULLを返す

    • 一つも見つからなければNULLを返す

  • 「合体する」と訳される

    • 他の言語でも「null合体演算子 (null coalesce operator)」ってのがありますね
    • PHP、C#では a ?? b というような2項演算子

6. 集約関数とNULL

極値関数とおなじ

同じことの繰り返しなので略

まとめ

  1. NULLは値ではない
  2. 値ではないので、述語もまともに適用できない
  3. 無理やり適用するとunknownを生じる
  4. unknownが論理演算に紛れ込むと、SQLが直感に反する動作をする
  5. これに対処するには、段階的なステップに分けてSQLの動作を追うことが有効

NOT NULL制約を活用する

COLUMN 文字列とNULL

演習問題

4-1

サンプル

sqliteonlineの環境では、NULLはASCソートで最初に来るよう

4-2

サンプル

sqliteonlineの環境では、NULLと文字列との結合結果はNULLになるよう

4-3

リファレンス

COALESCE

CASE式の糖衣構文らしい

NULLIF

「変更のあったものだけを集計する」といった時に使える

NULLIF(`変更後`,`変更前`)
-- 変更がない場合NULLに評価され、集計関数の対象から外れる