SQL修行vol.5

『達人に学ぶSQL徹底指南書』ー4.3値理論とNULL

前章で「再帰的集合」とか出てきて、集合苦手だったなぁ・・・と思っていたのだが今日はさらに論理とか命題とか出てきそうで、ひゃっ🙃て感じの中始めることとする。（猫アレルギーなのに飼い猫を膝の上に乗せて愛でたせいで朝からくしゃみが止まらない🐈）

理論編

結論は、SQLの論理体系は"true","false","unknown"の3つの真理値を持つ3値論理である。普通のプログラミング言語（C言語とか）は"true","false"の2値論理。

🐣3値論理の元凶NULL
SQLにおけるNULL：1種類[unknown]
一般的なNULL：2種類「未知[Unknown]（今はわからない）」「適用不能[Not Applicable, Inapplicable]（どう頑張ってもわからない）」
→かつてSQLでも一般的なNULLと同じように2種類に区別するべきと考えた人もいたが、結局1種類派が残った。

🐣なぜ「＝NULL」はエラーになるのか
正しくは「IS NULL」である。理由は、NULLに比較述語を適用したら結果は常に"unknown"になるから（∵NULLは値でも変数でもない）。

🐣"真理値unknown"と"NULLの一種UNKOWN"は別物
いよいよ意味がわからなくなってきたが、落ち着いて書き進める。混乱しそうなので、前者を小文字、後者を大文字で書くことにする。
真理値unknown：真理値型のれっきとした値（急に「れっきとした」ってどういう意味？って思って調べたところ「立派な、正真正銘の、正式な、由緒正しい」だそうだ）
NULLの一種UNKOWN：値でも変数でもないもの

--unknownの比較
unknown = unknown       /*"true"*/

--UNKOWNの比較
UNKOWN = UNKOWN         /*"unknown"*/

🐣3値論理の真理表
つまり、"true" AND "true"、"unknown" OR "false"…etcだったら結果はどうなる？をまとめた表のこと。
ここで覚えておくべきは、次の優先順位があるってこと。

• AND：false > unknown > true

• OR：true > unknown > false

実践編

🐣比較述語とNULL　その１ー排中律が成立しない
排中律：2値論理における「命題とその否定をまたはでつなげてできる命題は全て真である」という命題のこと（まだよくわからん）。つまり、白黒はっきり命題の真偽が定まるという原理のこと（まぁなんとなくわかった）。（ってか、これ実践編じゃなかったの）

ここで覚えておきたいのは、排中律はNULLを含む場合、SQLでは必ずしも成立しないということ。

例：年齢が20歳か、20歳でない生徒を選択せよ

--一見合ってそうなSQL
SELECT
   *
FROM
   Students
WHERE
   age = 20
   OR
   age <> 20;


--ageがNULLのレコードがあるとすると
--STEP1
SELECT
   *
FROM
   Students
WHERE
   NULL = 20
   OR
   NULL <> 20;

--STEP2
/*NULLに比較述語を適用したらunknownなので*/
SELECT
   *
FROM
   Students
WHERE
   unknown
   OR
   unknown

--STEP3
/*unknown OR unknown はunknownなので*/
SELECT
   *
FROM
   Students
WHERE
   unknown

結果
現実世界では、このレコードの生徒は「年齢が20歳か、20歳でない生徒」に該当するにもかかわらず、レコードは取得されない。・・・現実世界とSQL世界は必ずしも一致しないよ、って話。

🐣比較述語とNULL　その２ーCASE式とNULL
上述の「なぜ「＝NULL」はエラーになるのか」と同じでCASE式の中でも「IS NULL」を使わないといけないよ、って話。

CASE
   WHEN col_1
      THEN 'A'
   WHEN col_1 is NULL
      THEN 'B'
END

🐣NOT INとNOT EXISTSは同値ではない
ここで覚えておきたいのは、INとEXISTSは同値であるが、NULLのレコードがある場合はNOT INとNOT EXSITSは同値にならない。
具体的には、
NOT IN：サブクエリで使用されるテーブルの選択列にNULLが存在する場合、”unknown”or "false"を返す。
EXSITS："true" or "false"のどちらかを返す。

🐣限定述語とNULL
限定述語：ALL（比較述語と併用して「〜全てと等しい」「〜全てよりも大きい」というように使われる）、ANY（INと同値なのであまり使われない）
ALLの中にNULLがあると、これまた結果が"unknown"or "false"が返ることになる。

🐣限定述語と極値関数は同値ではない
極値関数：MAXとかMINとか極値（極大値・極小値）を求める関数

例：
ALL（限定述語）：彼は東京在住の生徒の誰よりも若い
極値関数               ：彼は東京在住の最も若い生徒よりも若い

上記例では、どちらも同じ意味のレコードが取得できそうだが・・・
NULLがあると
　ALL（限定述語）：うまくいかない（「限定述語とNULL」参照）
　極値関数               ：うまくいく（∵集計の際にNULLを排除するという特性がある）
入力が空テーブル（空集合）だったら
　ALL（限定述語）：全レコードが返る
　極値関数               ：NULLが返る。（ちなみに、COUNT関数以外の集約関数もNULLが返る）
というわけでこれらは同値ではない。

🐣所感
概念として理解はした、って感じではあるが、どうやら深そうなところに足を突っ込んだ感が否めなくてわくわくする。