SQLのWHERE句でNOTを使うときに注意すべきNULLの処理

概要

SQL の WHERE 句で NOT を利用してフィルタリングする際には NULL の考慮が必要となるのですが、その考慮をしなかったことでプログラムにバグを発生させてしまったため備忘録として残します。

下記のように 3 件のレコードもつデータを前提として 1 以外のデータを抽出する方法を検討してみます。

col_1
1
2
null

1 以外を抽出するために単純に NOT の条件（NOT(col_1 = "1")）でフィルタリングしてみます。1 以外のデータであるため 2 と NULL が出力されることを期待したのですが、残念ながら 2 のみ抽出されてしまいました。

NULL も抽出するためには下記のように明示する必要があります。否定の条件で記述することでいわゆる MECE になったと勘違いしてしまうのですが、 NULL があることも忘れないようにしようねという話でした。

ご興味がある方は、Databricks での実行コードを共有しますので、自分が利用しているデータベースで検証してみてください。

検証手順

1 以外の条件でフィルタリング

WITH test_data AS (
  SELECT '1' AS col_1
  UNION ALL
  SELECT '2' AS col_1
  UNION ALL
  SELECT NULL AS col_1
)
SELECT
  *
  FROM
    test_data
  WHERE 
    NOT(col_1 = "1")

NULL も考慮した上で 1 以外の条件でフィルタリング

WITH test_data AS (
  SELECT '1' AS col_1
  UNION ALL
  SELECT '2' AS col_1
  UNION ALL
  SELECT NULL AS col_1
)
SELECT
  *
  FROM
    test_data
  WHERE 
    NOT(col_1 = "1") OR col_1 IS NULL

比較演算子により NULL も考慮した上で 1 以外の条件でフィルタリング

下記についてはデータベースによっては対応していない可能性があります。

%sql
WITH test_data AS (
  SELECT '1' AS col_1
  UNION ALL
  SELECT '2' AS col_1
  UNION ALL
  SELECT NULL AS col_1
)
SELECT
  *
  FROM
    test_data
  WHERE 
    NOT(col_1 <=> "1")