これは何
SQLを書いているとき、Nullableなカラムを絞り込みや結合の条件式に使うことはよくあると思います。
Nullableなカラムを使うときは、ミスをしやすい気をつけないといけない点があるので、Tipsとして残します。
コメントでjnchitoさんから補足をいただきました
https://qiita.com/getty104/items/3c106fd976c4f6a8c5ac#comment-4eccca1a9834463e2eef
テーブルの例
今回の解説では、↓のようなテーブルを対象にクエリを描きます
- テーブル名: users
- certifiedカラム: 認証済みのユーザーかどうか
- true → 認証済
- false → 認証却下
- NULL → 未認証
| id | certified |
|---|---|
| 1 | false |
| 2 | true |
| 3 | NULL |
よくミスしてしまうパターン
認証済か、未認証のユーザーデータを取得することを想定し、以下のクエリを書きます。
SELECT *
FROM users
WHERE certified != false
一見あっていそうなクエリですが、
実はこのクエリでは正しいデータは取得できません。
なぜこのクエリでは意図したデータが取得できないかを開設します。
NULLの挙動について
何が間違っているかを説明する前に、NULLの挙動について説明します。
NULLは比較演算子に使われると、基本的にNULLを返すという性質があります。
実際に以下のようなクエリを実行してみます。
SELECT (NULL != TRUE) IS NULL, (NULL OR TRUE) IS NULL, (NULL AND TRUE) IS NULL
すると結果は以下のようになりました
| col1 | col2 | col3 |
|---|---|---|
| true | true | true |
つまり、NULLが条件式に使われた時点で結果はNULLになります。
また、絞り込みや結合の評価としてNULLが指定されると、それはfalseとして評価されます。
つまり、条件式にNULLが使われた時点で問答無用でFALSEになってしまいます。
何が間違っていたか
今回条件式に指定しているcertifiedは、Nullableで、今回は値がtrueのものと、NULLのものをとってくる必要がありました。それを意図してcertified != falseとしていましたが、これでは値がNULLのデータは取得できない、ということになります。
正しくは、IS NULL演算子を使って、NULLであるものも取得することを明示する必要がありました。
SELECT *
FROM users
WHERE (certified = true) OR (certified IS NULL)
結論
Nullableなカラムを条件式に使う場合は、IS NULLを使う必要があるかどうか必ず考えるようにしましょう。