SQL isnullの使い方 NULL判定やNULL置換の方法 case式でも対応可能
  • facebookページ
  • twitterページ
  • 2019.09.05

    SQL isnullの使い方 NULL判定やNULL置換の方法 case式でも対応可能

    NULL値の検索をおこなう場合に使う、IS NULLについてまとめています。

    以下、データベースとして、MySQLのサンプルデータベースEmployeesを使っています。SQL実行結果の表示にはphpMyAdminを使用しています。

    IS NULL 演算子の使い方

    カラム名=NULLという指定ではデータを抽出できないので、IS NULL演算子を使います。

    WHEREの条件指定で使うことが多いです。

    NULL値を1件、データ追加し、上記SQLを実行しました。

    具体的には、employeesデータベースのsalariesテーブルのsalaryカラムをNULL可に設定し、以下のデータを追加しました。

    IS NULLの実行速度が遅い場合は、インデックスで改善可

    IS NULLやIS NOT NULLはインデックスを利用できないので遅いという情報がネットで見つかりました。単独でIS NULLを使うと、全件走査となるので遅くなるというんですね。

    MYSQL 5.7で試してみました。

    • 1 total, Query took 0.5021 seconds.

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

    • 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の場合に返す値)

    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 比較関数と演算子

    関数の引数にサブクエリを使うことも可能です。

    ゼロとNULLを同じに扱うには

    DBMSによって、空文字とNULLの扱いが異なります。

    対処方法の一つは、where句に

    カラム名 IS NOT NULL AND カラム名 != ”

    を指定します。

    もうひとつは、NULL値を空文字に変換する方法です。

    実行結果は以下のようになりました。(MySQL5.7の場合)

    SQLの実行結果

    0の場合0、aの場合a、NULLの場合0となりました。

    空文字とNULLを同じに扱うには

    ゼロではなく空文字と同じに扱うには、coalesce(col, ”) でNULLを”に変換すればOKです。

    case式でも代用するサンプル

    下記のcase式を使ってNULLの変換が可能です。

    • case when 項目名 is null then 式1 else 式2 end

    以下は、NULLを空文字に変換するSQLです。

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

    ポテパンが提供するサービスについて

    本メディア「ポテパンスタイル」を運営する株式会社ポテパンは、エンジニアキャリア領域で複数サービスを提供しています。

    ポテパンフリーランス

    ポテパンフリーランス

    フリーランスエンジニアの方に高単価案件をご紹介しております。弊社ではフリーランス案件を常時300件ほど保有しており、その中からあなたに適した案件をご案内いたします。また、これから独立してフリーランスになる方の無料個別相談も承っております。フリーランスになった後の案件獲得方法やお金面(税金や保険など)についてお答えいたします!フリーエンジニアになりたい方向けのコンテンツも盛りだくさんです。

    ポテパンキャリア

    ポテパンキャリア

    エンジニア職専門の転職エージェントです。ポテパンキャリアでは、技術のわかるエージェントがあなたの転職をサポートします。エージェント自身がエンジニアなので、あなたと同じ目線で仕事内容や今後のキャリアについて一緒に考えることができます。年収800万円以上のハイスペック転職をご希望の方は「ポテパンプロフェッショナル」もご用意しておりますのでご利用下さいませ。

    ポテパンキャンプ

    ポテパンキャンプ

    ポテパンキャンプでは、RubyにてゼロからオリジナルのECサイトを作り上げてる3ヶ月間の実践型カリキュラムを提供しております。すでに本スクールの卒業生は、エンジニア職として様々な企業様に就職しております。なお、本スクールは受講料25万円と他社スクールに比べ格安となっており、またポテパンからご紹介させていただいた企業へ就職が決まった場合は、全額キャッシュバックいたします。



    株式会社ポテパンは、企業とエンジニアの最適なマッチングを追求しています。気になるサービスがあれば、ぜひ覗いてみてください!

    ポテクラバナー ポテプロバナー

    この記事をシェア

    • Facebookシェア
    • Twitterシェア
    • Hatenaシェア
    • Lineシェア
    pickup









    ABOUT US

    ポテパンはエンジニアと企業の最適なマッチングを追求する企業です。

    READ MORE

    ポテパンおすすめ案件