NULLについて

投稿日：2016年11月8日

「達人に学ぶSQL徹底指南書」を読み進めていきましたがNULLについていろいろ書いてあるのでメモリます。

Contents

1 NULLには未知と適用不能の2パターンがある
2 = NULL が使えず IS NULLと書かなくてはいけない理由
3 排中律が存在しない
4 CASE式とNULL
5 NOT INとNOT EXISTSは同値ではない

NULLには未知と適用不能の2パターンがある

初めて知りましたが、NULLにも未知と適用不能の2パターンがあるようです。

未知・・今はわからないけど、条件がそろえば知ることができる
適用不能・・どう頑張ってもわからないもの

ただ実務上ではこの２つはほぼ同一に考えられているようですね。

= NULL が使えず IS NULLと書かなくてはいけない理由

要点をまとめると下記のようになるでしょうか。

NULLは値や変数ではなく、NULLというのはそこに値がないことを示す視覚的なマークのようなもの
またプログラミング言語でいうNULLとSQLのNULLとは全く別物
IS NULLというのはISとNULLの組み合わせよりはこれ1つで状態を表している
ブーリアンはtrue,false以外にもunknownという状態が存在する。

サンプルパターン
a=2,b=5,c=NULLだと仮定
a<b AND b >c　unknown
a>b OR b<c unknown
a<b OR b<c true
NOT ( b<>c) unknown

排中律が存在しない

name,ageだけのテーブルがあり、ある人物のageがNULLとすると、下記SQLでは選択できない

SELECT 
  * 
FROM 
  member
WHERE
  age >= 20 or age < 20  --20才以上か未満か

SELECT

FROM

member

WHERE

age >= 20 or age < 20 --20才以上か未満か　

例えば上記のSQLの場合、人間の感覚でいうとすべての人は20歳以上もしくは20歳未満ですのですべてが該当しそうですが、NULLの場合はわからないとなって出てこなくなります。

CASE式とNULL

CASE col_1
  WHEN 1 THEN '○'
  WHEn NULL THEN '×'
END

CASE col_1

WHEN 1 THEN '○'

WHEn NULL THEN '×'

END　

上記のSQLでは×を返すことがありません。col_1 = NULLという評価をするためです。この場合、NULLを判定したい場合は、下記のように書きます。

CASE 
  WHEN col_1 = '1' THEN '○'
  WHEN col_1 is NULL  THEN '×'
END

CASE

WHEN col_1 = '1' THEN '○'

WHEN col_1 is NULL THEN '×'

END　

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

Class_A

name	age	city
ヤマダ	23	神奈川
タロウ	22	千葉

Class_B

name	age	city
サイトウ	22	東京
ワタナベ		東京
キムラ	23	千葉

上記のようなテーブルがあり Class_Bの東京在住の生徒と年齢が一致しないAクラスの生徒を選択するクエリを書くとします。期待する結果としては「ヤマダ」たとれればOKです。

SELECT 
  *
FROM  Class_a  
WHERE　age NOT IN  ( SELECT age FROM Class_B WHERE city ='東京');

SELECT

FROM Class_a

WHERE　age NOT IN ( SELECT age FROM Class_B WHERE city ='東京');

これですと実は1人も選択されません。(もちろん

SELECT age FROM Class_B WHERE city ='東京'

1	SELECT age FROM Class_B WHERE city ='東京'

であれば22が引っかかります。

これはNOT～が具体的にはunknown(含まれているか、含まれていないかがわからない)を返すから判定できない、となってしまうのです。ちなみにNOT EXISTSにかえると、意図した検索結果（ヤマダ）が帰ってきます。(※EXISTSはtrueかfalseを必ず返すため。)

SELECT 
  *
FROM  Class_a  A
WHERE
  NOT EXISTS  ( SELECT age FROM Class_B B WHERE B.city ='東京' and A.age = B.age );

SELECT

FROM Class_a A

WHERE

NOT EXISTS ( SELECT age FROM Class_B B WHERE B.city ='東京' and A.age = B.age );

詳しくは「達人に学ぶSQL徹底指南書」を読んでみましょう。

それ以外でも集約関数などでNULLが悪さをすることがあり、NULLは様々な誤動作をすることがあります。

対策としてはNULL自体を使わずにNOT NULLをなるべく使うというのが正しいテーブルの作り方になります。