受講料が最大70%OFF 受講料が最大70%OFF

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

以下を実行して、salariesテーブルのsalaryカラムにインデックスを設定後、同じSQLを実行します。

ALTER TABLE `salaries` ADD INDEX `salary_index` (`salary`);

実行時間が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 Oracle言語リファレンス

Oracleは、他のDBMSと比べるとNULLの扱いが特殊です。空文字をNULLとして処理する点やNULLに10を加算してもNULLになる点など、知らなければハマってしまいますよね。

Oracleの公式ドキュメントにも「Oracle Databaseは、長さがゼロの文字値をNULLとして処理します。ただし、この処理はOracleの今後のバージョンでも継続されるとは限らない…」とあります。

Oracleでは特に意識して、NVL関数などを使ってNULL値を変換しておくのが安全と言えるでしょう。

COALESCE

SQL Server、Oracle、MySQLなどの主要DBMSで使える関数です。

COALESCE(カラム1,カラム2,カラム3,カラム4,…,値)として使います。

カラム1がNULLならカラム2、カラム2がNULLならカラム3…、全て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の変換が可能です。

以下は、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を別の値に置き換える関数が用意されている

エンジニアになりたい人に選ばれるプログラミングスクール「ポテパンキャンプ 」

ポテパンキャンプは卒業生の多くがWebエンジニアとして活躍している実践型プログラミングスクールです。 1000名以上が受講しており、その多くが上場企業、ベンチャー企業のWebエンジニアとして活躍しています。

基礎的な学習だけで満足せず、実際にプログラミングを覚えて実践で使えるレベルまで学習したいという方に人気です。 プログラミングを学習し実践で使うには様々な要素が必要です。

それがマルっと詰まっているポテパンキャンプでプログラミングを学習してみませんか?

卒業生の多くがWebエンジニアとして活躍

卒業生の多くがWeb企業で活躍しております。
実践的なカリキュラムをこなしているからこそ現場でも戦力となっております。
活躍する卒業生のインタビューもございますので是非御覧ください。

経験豊富なエンジニア陣が直接指導

実践的なカリキュラムと経験豊富なエンジニアが直接指導にあたります。
有名企業のエンジニアも多数在籍し品質高いWebアプリケーションを作れるようサポートします。

満足度高くコスパの高いプログラミングスクール「ポテパンキャンプ」

運営する株式会社ポテパンは10,000人以上のエンジニアのキャリアサポートを行ってきております。
そのノウハウを活かして実践的なカリキュラムを随時アップデートしております。

代表の宮崎もプログラミングを覚えサイトを作りポテパンを創業しました。
本気でプログラミングを身につけたいという方にコスパ良く受講していただきたいと思っておりますので、気になる方はぜひスクール詳細をのぞいてくださいませ。