「達人に学ぶSQL徹底指南書」を読み進めていきましたがNULLについていろいろ書いてあるのでメモリます。
Contents
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では選択できない
|
1 2 3 4 5 6 |
SELECT * FROM member WHERE age >= 20 or age < 20 --20才以上か未満か |
例えば上記のSQLの場合、人間の感覚でいうとすべての人は20歳以上もしくは20歳未満ですのですべてが該当しそうですが、NULLの場合はわからないとなって出てこなくなります。
CASE式とNULL
|
1 2 3 4 |
CASE col_1 WHEN 1 THEN '○' WHEn NULL THEN '×' END |
上記のSQLでは×を返すことがありません。col_1 = NULLという評価をするためです。この場合、NULLを判定したい場合は、下記のように書きます。
|
1 2 3 4 |
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です。
|
1 2 3 4 |
SELECT * FROM Class_a WHERE age NOT IN ( SELECT age FROM Class_B WHERE city ='東京'); |
これですと実は1人も選択されません。(もちろん
|
1 |
SELECT age FROM Class_B WHERE city ='東京' |
であれば22が引っかかります。
これはNOT～が具体的にはunknown(含まれているか、含まれていないかがわからない)を返すから判定できない、となってしまうのです。ちなみにNOT EXISTSにかえると、意図した検索結果（ヤマダ）が帰ってきます。(※EXISTSはtrueかfalseを必ず返すため。)
|
1 2 3 4 5 |
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をなるべく使うというのが正しいテーブルの作り方になります。
