NULL値の検索をおこなう場合に使う、IS NULLについてまとめています。
以下、データベースとして、MySQLのサンプルデータベースEmployeesを使っています。SQL実行結果の表示にはphpMyAdminを使用しています。
目次
IS NULL 演算子の使い方
カラム名＝NULLという指定ではデータを抽出できないので、IS NULL演算子を使います。
WHEREの条件指定で使うことが多いです。
select * from salaries where salary IS NULL
NULL値を1件、データ追加し、上記SQLを実行しました。
具体的には、employeesデータベースのsalariesテーブルのsalaryカラムをNULL可に設定し、以下のデータを追加しました。
IS NULLの実行速度が遅い場合は、インデックスで改善可
IS NULLやIS NOT NULLはインデックスを利用できないので遅いという情報がネットで見つかりました。単独でIS NULLを使うと、全件走査となるので遅くなるというんですね。
MYSQL 5.7で試してみました。
select * from salaries where salary IS NULL
- 1 total, Query took 0.5021 seconds.
以下を実行して、salariesテーブルのsalaryカラムにインデックスを設定後、同じSQLを実行します。
ALTER TABLE `salaries` ADD INDEX `salary_index` (`salary`);
- 1 total, Query took 0.0004 seconds.
実行時間が1/1255に改善されました。
インデックスの設定以外に、where句の条件にandで条件を追加することで対象件数を絞り込み、パフォーマンスを改善する方法もあります。
ISNULL関数は、名前が似ているがIS NULL演算子とは別物
IS NULL演算子とは全く別に、ISNULL関数というものがあります。
SQL Serverで使える、NULLを別の値に変換する関数です。同じような動きをする関数が、各種用DBMSで用意されています。
ISNULL
SQL Serverで利用可能です。
ISNULL(カラム名,’NULLの場合の文字列’）として使います。
見た目がIS NULL演算子と似ているので混同しやすいのが難点です。SQL Serverには同じ動作を実現するCOALESCE関数も用意されているので、そちらを使うことも可能です。
ISNULL (Transact-SQL) – SQL Server | Microsoft Docs
なお、SQL Serverでは、ANSI_NULLSというオプションによって、NULL=NULLが偽（False）になるケースがあります。
参考）NULL の取り扱い方 – SQL Server 入門
NVL
Oracleで使える関数です。NVLとNVL2があります。
- NVL(対象カラム,NULLの場合に返す値）
- NVL2（対象カラム,NULLでない場合に返す値,NULLの場合に返す値)
Oracleは、他のDBMSと比べるとNULLの扱いが特殊です。空文字をNULLとして処理する点やNULLに10を加算してもNULLになる点など、知らなければハマってしまいますよね。
Oracleの公式ドキュメントにも「Oracle Databaseは、長さがゼロの文字値をNULLとして処理します。ただし、この処理はOracleの今後のバージョンでも継続されるとは限らない…」とあります。
Oracleでは特に意識して、NVL関数などを使ってNULL値を変換しておくのが安全と言えるでしょう。
COALESCE
SQL Server、Oracle、MySQLなどの主要DBMSで使える関数です。
COALESCE（カラム１,カラム２,カラム３,カラム４,…,値）として使います。
カラム１がNULLならカラム２、カラム２がNULLならカラム３…、全てNULLなら最後の値を返すという動きをします。
NULLを別の値に変換するだけなら、COALESCE（カラム名,値）として使います。
本来の使い方は、連絡先に固定電話番号、携帯電話番号、メールアドレス、ツイッターIDとあった場合に最初に見つかったNULLでない値を返す、といったケースに使えます。
MySQL :: MySQL 5.6 リファレンスマニュアル :: 12.3.2 比較関数と演算子
関数の引数にサブクエリを使うことも可能です。
select coalesce( ( select null ), 0) as value )
ゼロとNULLを同じに扱うには
DBMSによって、空文字とNULLの扱いが異なります。
対処方法の一つは、where句に
カラム名 IS NOT NULL AND カラム名 != ”
を指定します。
もうひとつは、NULL値を空文字に変換する方法です。
select coalesce(col, 0)
from (select '0' as col
union all
select 'a' as col
union all
select null as col) tbl
実行結果は以下のようになりました。（MySQL5.7の場合）
SQLの実行結果
0の場合0、aの場合a、NULLの場合0となりました。
空文字とNULLを同じに扱うには
ゼロではなく空文字と同じに扱うには、coalesce(col, ”) でNULLを”に変換すればOKです。
select coalesce(col, '')
from (select '0' as col
union all
select 'a' as col
union all
select null as col) tbl
case式でも代用するサンプル
下記のcase式を使ってNULLの変換が可能です。
- case when 項目名 is null then 式1 else 式2 end
以下は、NULLを空文字に変換するSQLです。
select col,case when col is null then '' else col end
from (select '0' as col
union all
select 'a' as col
union all
select null as col) tx
SQLの実行結果は以下の通りです。
SQLの実行結果
case式ではNULL以外の置換も可能というメリットがあります。ただし、記述量が増えるデメリットもあります。
【関連記事】
▶SQLのCASE式サンプル集 order byやgroup byとの組み合わせもバッチリ
IS NULLのまとめ
- NULL判定には、値=NULLではなく、値 IS NULLを使う
- IS NULLの実行速度は、インデックス設定やwhere句への条件追加で改善する可能性あり
- SQL ServerにはISNULL関数があるが、IS NULL演算子とは全く別物
- 各DBMSには、NULLを別の値に置き換える関数が用意されている
