達人に学ぶSQL徹底指南書 about books HOT 3 OPEN

4.３値論理とNULL

NULLは値ではない

NULLは値ではないため，比較する時はIS NULLを使う必要がある

unknownの優先順位

ANDの場合：false > unknown > true
ORの場合：true > unknown > false

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

NOT IN はunknownを返すが，NOT EXISTSはtrueとfalseしか返さないため，条件にunknownが発生する場合挙動がかわる．

ALLなどの限定述語とNULL

ALL述語は条件をANDで連結した論理式の省略形として定義されているため，条件にunknownが発生すると，なにもselectされなくなるという事が起こる

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

極値関数は集計の際にNULLを排除するという特性を持っている．
また，入力が空テーブルだった場合NULLを返す．そのため，ALL述語では比較対象が存在しない事により全部が選択されている場合でも，極値関数ではNULLが帰ってきて，何も選択されないということが起こる．

集約関数とNULL

COUNT以外の集約関数も，入力が空テーブルだった時NULLを返す．

まとめ

• NULLは値ではないので，述語もまともに適用できない
• 無理やる適用するとunknownが生じる
• unknownが論理演算に紛れ込むと，SQLが直感に反する動作をしてしまう
• 空文字とNULLもDBMSによってルールが異なる

from books.

5. EXISTS 述語の使い方

述語とは？

戻り値が真理値になる関数のこと．（true,false,unknown）

述語の種類

=やBETWEENなど1行を入力とする述語を「一階の述語」，EXISTSのように行の「集合」を入力とする述語を「二階の述語」と呼ぶ．

テーブルに存在しないデータを探す

これは『これこれこういう性質を満たす」という条件を設定するのではなく，そもそも「データが存在するか否か」という「二階の問い合わせになっている」．こういうときにEXISTS述語の出番．
全部が揃っていると仮定した場合の集合を作り，そこから現実に出席した人を引き算する．NOT EXISTS．

全称量化その１ 肯定⇔二重否定の変換

「全ての行について〜」というのは，「〜でない行が１つも存在しない」に変換できる．これをNOT EXISTSで表現する．
また，全ての行について「1. 算数の点数が80点以上」で「2. 国語の点数が50点以上」である，というものも同様．

全称量化その２ 集合vs.述語

EXISTSとHAVINGの違い

どちらでも書ける場合が多いが，HAVINGのほうが直感的にわかりやすい．
しかし，NOT EXISTSは1行でも満たさない行が存在すれば検索を打ち切るため，パフォーマンスが良い．
また，HAVINGを使うと問答無用で集約されてしまうが，NOT EXISTSならば結果に含められる情報が多い．

列に対する量化

SQLは列に対する量化に対処する述語も持っている．
全ての：ALL
どれかが：ANY （またはINでもよい）
※NULLを対象にこれをやりたい時は，COALESCE関数を使う

まとめ

• SQLにおける述語とは，真理値を返す関数のこと
• EXISTSだけた，他の述語と違って（行の）集合を引数に取る
• その点で，EXISTSは高階関数の一種とみなせる
• SQLには全称量化に相当する演算子がないので，NOT EXISTSで代用する

from books.

6. HAVING 句の力

HAVING句とは、GROUP BY句で作成されたグループ表に対して検索条件を与えて行を絞り込むことである。

データの歯抜けを探す

例えば，連番ファイルの歯抜けを探すとしたら，

SELECT '歯抜けあり' AS gap
  FROM SeqTbl
HAVING COUNT(*) <> MAX(seq)

などとする．番号の最大値が行の総数と一致するかどうか．

欠番を探す -発展版

--歯抜けの最小値を探す
SELECT CASE WHEN COUNT(*) = 0 OR MIN(seq) > 1 --下限が1出ない場合→1を返す
         THEN 1
         ELSE (SELECT MIN(seq+1)  -- 下限が1の場合→最小の欠番を返す
                 FROM SeqTbl S1
                  WHERE NOT EXISTS
                          (SELECT * FROM SeqTbl S2 WHERE S2.seq = S1.seq + 1))END
FROM SeqTbl;

HAVING 句でサブクエリ -最頻値を求める

GROUP BY により，同じ量の行をグループ化 → HAVING句で最も数が多いグループを検索．

from books.