SQLにおけるNULLの取り扱いガイド: 3値論理の落とし穴を回避する

[takegue]

SQLの取り扱いで最もトラブルの多い 仕様の1つが NULL に関する取扱いだろう。
例として、次のクエリを確認する。

with data as (
  select 'key1' as key, "_name_a"  as name, "X" as label
  union all   
  select 'key2' as key, "_name_b"  as name, NULL as label
  union all   
  select 'key3' as key, "_name_c"  as name, "Y" as label
)

select 
   count(1) 
from 
  data
where 
   label != "X"

-- >  1(actual)   != 2 (expected)

実際に数えたいXではないレコードは、2にも関わらず実際の出力は1しか得られない。

解説と対処方法

SQLにおいては、真理値(true, false)に加えて NULLを含めた 3値論理の扱いが原則となる。
そして、where/having では nullの取り扱いは すべて not true として取り扱われる.

null をハンドリングするには

• ifnull / coalesce : null時に適当な値でフォールバックする関数
• is distinct from: 引数がnullでも返り値にNULLを返さない演算子

といった方法が利用できる

-- ifnull によるハンドリング
-- 省略
select 
   count(1) 
from 
  data
where 
   ifnull(label, "Unknown")  != "X"

-- is distinct from によるハンドリング
-- 省略
select 
   count(1) 
from 
  data
where 
  label is not distinct from  "X"

注意点として, ifnull や coalesceでハンドリングする場合は、NULL同士の比較には留意する必要がある。
仕様上は NULLは true = NULL is distinct from NULL となる。
ifnullなどでハンドリングした際に一つの値に丸めことに対する弊害には留意しよう。
余談ではあるが、NULLに似た取り扱いである NaNの取り扱いでも同様といえる。

3値論理(Thee-Valued Logic)

標準的なSQLにおいて、論理値の扱いは 2値ではなくNULLを含めた3値論理ですべて考える必要がある。

比較演算 と NULL

SQL上で定義されるビルトインの演算子は、オペランドのいずれかを含むNULLを含む計算は
出力がNULLになることを覚えておく必要がある。
例えば A <= B の場合 AかBがNULLであれば、この比較演算の結果は NULL となる。

struct での取り扱い

NULLに関する取扱いで特殊となるのがstruct型がサポートする比較演算の演算(=, !=, <>, IN)である。
struct型ではstruct型同士での型比較が可能である。 (このことからJOINのusing に含むことが可能である)

nullを含むstruct型では 次の例のような挙動を行う。

select struct(1, null) = struct(2, null)
--> false

select struct(2, null) = struct(2, null)
--> null

select struct(null, null) = struct(2, null)
--> null

これはという挙動で、 struc型の等価比較は各フィールドの等価比較を論理積AND(論理演算の項を参照)で結合した結果と等価である。

この動作は直感的にはかなり問題を生みやすいため、struct型の等価比較の利用はおススメしない
もしstruct型の比較を利用する場合には、次のようにformat演算などで文字列化しての比較をオススメする。
この場合においてはnullの等価性の定義としては不正確な演算ではあるが、
人間にとっては比較的直感的な取り扱いとして利用できる。

select format('%t', struct(2, null)) = format('%t', struct(2, null))
--> true

論理演算

ここまでNULLを含む演算子での取り扱いを考えてきたが、三値論理を考えるうえで
さらに複雑になるのが論理演算での取り扱いである。

次の3値での論理演算での結果を確認してほしい。

select true AND NULL 
--> null

select true AND NULL 
--> null

select true OR NULL 
--> true

select false OR NULL 
--> null

特にNULLに関する取扱いで間違えることが多いのが
論理積ANDにおけるNULLの取り扱いである、whereで複数条件を利用する際に

 -- ...
  where 
      condition1
      AND condition2
      AND condition3

と頻出するこの表現が正しく機能するためには
condition1-3の結果がすべてNULLでなければならない
1つの条件でもNULLが含まれる場合、全体として null, つまり not true となるため条件に合致しなくなる。

3値論理演算の真理値表

T = true, U = NULL, F = false として、真理値表を記述すると次のようになる。
3値論理においてNULLは不定(trueでもfalseでも良い)としてとり扱うことで理解がしやすいだろう

NOT

NOT演算は入力がNULLであれば、出力もNULLという扱い

V1	T	U	F
	F	U	T

AND

V1\V2	T	U	F
T	T	U	F
U	U	U	F
F	F	F	F

ANDの演算は 一方のオペランドがfalseの時、他の値が不定(NULL)であろうとtrueであろうと
全体をfalseと判定できる。

• not any(e is false)

OR

V1\V2	T	U	F
T	T	T	T
U	T	U	U
F	T	U	F

ORの演算は 一方のオペランドがtrueであれば、他の値が不定(NULL)であろうとtrueであろうと
全体をtrueと判定できる。

悪魔合体: 比較演算と論理演算の混合

さてここまでで論理演算によるNULLの取り扱いを確認してきた。
次のケースにおいて、XとYがどのような場合にNULLとなるか確認してみてほしい

1. NOT ((10<= X) OR (Y < 20))
2. NOT ((10<= X) AND (Y < 20))

集計関数における nullの取り扱い

最後にNULLの取り扱いとなるのは、 集計関数だ。

https://cloud.google.com/bigquery/docs/reference/standard-sql/aggregate-function-calls

集計関数においてもNULLの考慮が必要となる。

• count: nullでも1つとして数え上げられる
• sum: nullの場合は無視される(≒0として加算)
• avg: 分母はcount, 分子はsum として数え上げられる。
• min, max: NULLの値は無視されて計算される

avgはcountとsumの異なる挙動が複合的に動くため、カラムのNULLの比率が不透明の場合での利用を推奨しない。
利用する場合は IGNORE NULLS/RESPECT NULLS といった形で, NULLを明示的にに利用するか否かを示すことをお勧めする。

ORDER句におけるNULLの取り扱い

ODER BYにおける並び替えにおいて、NULLは デフォルトでは 昇順で必ず最初に来る値 としてふるまう。
つまり、どの値よりも小さい値として扱われる。
実はこの挙動は、変更可能である。 ORDER BY句のオペランドに後に
次の句によって NULLを度の値よりも小さい値か、NULLをどの値よりも大きい値を選択的に選ぶことができる

https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax#order_by_clause

• ORDER BY A ASC NULLS FIRST
• ORDER BY A AC NULLS LAST