NOT LIKEでNULLがあるカラムの集計処理を走らせると少し困ったことになる

概要

NULLがあるカラムはLIKEでもNOT LIKEでも抽出できない。NULLが存在するカラムで〇〇という文字列が含まれる場合(LIKE)と〇〇という文字列が含まれない場合(NOT LIKE)の和をとると全体の行数に一致しない

伝えたいこと

NULLがある行でLIKE (NOT LIKE)を使用するときは気をつけよう。

内容

〇〇という文字列が含まれる場合(LIKE)と〇〇という文字列が含まれない場合で含まれない場合で全体の行数と一致させたい場合はどうすれば良いのか？

単純にNULLが含まれないと見なすのであれば、NOT LIKEによる集計だけではなく IS NULL の行もCOUNTして集計してあげれば全体の行数と一致する。

全体の行数の関係式

全体の行数 = LIKE + NOT LIKE + IS NULL

実例

tosaという文字列が含まれる場合：

SELECT COUNT(name) FROM `users` WHERE name LIKE '%tosa%' 

tosaという文字列が含まれない場合（nameカラムはNULLを含む）：

SELECT COUNT(name) FROM `users` WHERE name NOT LIKE '%tosa%' OR name IS NULL

重要なポイント

NULLの扱いについて

• LIKE演算子: NULLに対してはマッチしない（結果はNULL）
• NOT LIKE演算子: NULLに対してはマッチしない（結果はNULL）
• IS NULL: NULLかどうかを明示的にチェック

実践的な対策

1. 明示的なNULLチェック: NOT LIKE使用時はOR name IS NULLを追加
2. COALESCE関数の活用: NULLを空文字列に変換してから処理
3. 事前のデータクレンジング: NULLの有無を事前に確認

-- COALESCE を使った例
SELECT COUNT(*) FROM `users` 
WHERE COALESCE(name, '') NOT LIKE '%tosa%'

まとめ

SQLでの文字列検索において、NULLの存在は予想外の結果を招くことがある。特に集計処理では、LIKE条件とNOT LIKE条件の結果を合計しても全体の行数と一致しない場合があるため、NULLを含めることを忘れないように注意が必要です。