SQLではNULLの取り扱いに要注意 その２

Posted on by つちえ

前回、NULLが値でないこと・NULLと比較述語を組み合わせた場合の動作について書きました。

今回は、前回の内容もとに、何となくNULLを扱ってしまうことで起こる問題点と解決策を

以下の例をもとに、書いてみようと思います。

  • Aクラス(CLASS_A)
USER_ID AGE
1 25
2 30
3 35

 

  • Bクラス(CLASS_B)
USER_ID AGE
4 30
5 25
6 NULL

 

  • 条件
    1. 年齢は必須入力でないため、NULLで登録されることがあり得る。
    2. 同じユーザーがAクラス・Bクラスに同時に属することはない。

こんな感じのテーブルから、クラスBの誰とも年齢が一致しない、クラスAのユーザ一覧を

取得してみようと思います。

本来ならユーザ3は、クラスBの誰とも年齢が一致しないので

ユーザ3のレコードが取得できそうですが、こんな感じのSQLを書いてしまうと取得することができません。

SELECT 
    *
FROM
    CLASS_A AS a
WHERE
    a.AGE NOT IN (SELECT 
            b.AGE
        FROM
            CLASS_B AS b)
;

 

正しく取得できない理由は、SQL文が以下のような順で評価されるためです。
（長くなるので、以下の例ではWHERE句以降のみを抜粋）

  1. CLASS_Bに関するSQLを展開 
    WHERE
    a.AGE NOT IN (30, 25, NULL)
  2. WHERE句を展開 
    WHERE
    NOT ( a.AGE = 30 OR a.AGE = 25 OR a.AGE = NULL )
  3. WHERE句にド・モルガンの法則を適用 
    WHERE
    a.AGE <> 30 AND a.AGE <> 25 AND a.AGE <> NULL
  4. NULLと比較述語を組み合わせると、UNKNOWNと評価される。 
    WHERE
    a.AGE <> 30 AND a.AGE <> 25 AND UNKNOWN
  5. ANDとUNKNOWNが組み合わさると、FALSEに評価される。 
    WHERE
    FALSE

4.となる理由がわからない方は前回のブログを、

5.がわからない方はクリーネの3値論理 真理値表を見ていただければと思います。

対応策１

そもそも年齢がNULLのレコードは、検索結果に含める必要性がないため、検索条件を変更します。

SELECT 
    *
FROM
    CLASS_A AS a
WHERE
    a.AGE IS NOT NULL
        AND a.AGE NOT IN (SELECT 
            b.AGE
        FROM
            CLASS_B AS b
        WHERE
            b.AGE IS NOT NULL)
;

 

ただこれだと、検索条件の漏れが起こってしまうため、イマイチな気がします。

対応策２

サブクエリ部分(CLASS_Bに関するSQL)部分が先に評価されることを防ぐため、

NOT INをNOT EXISTSに置き換えます。

対応策１で起こる検索条件の漏れを防ぐことができますので、個人的にはこちらがお勧めです。

SELECT 
    *
FROM
    CLASS_A AS a
WHERE
    NOT EXISTS ( SELECT 
            b.AGE
        FROM
            CLASS_B AS b
        WHERE
            a.AGE = b.AGE)
;

 

以上。