oracleでnullを比較する方法！失敗しない基本と応用を解説します

Oracleでのデータ操作において、oracle 比較 nullの扱いは多くの開発者が直面する課題です。

オラクルのNULLとは何ですか？と問われた時、その本質を正確に理解しているでしょうか。

例えば、OracleのNULLと0の関係性や、<>NULLの意味、sql null同士の比較がどうなるかといった基本的な疑問から、oracle number null判定の具体的な方法、さらにはSQLでどちらかがNULLになる場合の挙動まで、曖昧な知識はエラーの原因となります。

また、oracle nullをセットした後のデータ抽出で、oracle is not null 効かないと感じる場面や、sql nullを含むデータを正しく扱う方法、oracle nullの場合 置き換えやsql nullの場合 置き換えに使うCOALESCE関数の使い方など、実践的なテクニックも求められます。

この記事では、これらの疑問に網羅的に答え、OracleにおけるNULLの正しい扱い方を徹底的に解説していきます。

この記事で分かること

oracle上でnullを比較する際の基本

オラクルのNULLとは何ですか？

結論から言うと、OracleにおけるNULLとは、「値が存在しない」「データが不定または不明である」ことを示す特殊なマーカーです。

これは、何らかの値を持っている状態ではなく、値そのものが欠落している状態を表現するために使用されます。

例えば、社員データベースの「退職日」カラムを考えてみましょう。

在職中の社員にとって「退職日」はまだ決まっていないため、このカラムには具体的な日付を入れられません。

このような場合にNULLが使用されます。

NULLの主な特徴

NULLは、数値の「0」や長さゼロの空文字列「''」とは全く異なる概念です。

空文字列の扱いに関する注意点

Oracle Databaseは現在、空文字列をNULLとして処理しますが、公式ドキュメントでは将来のリリースでこの仕様が変更される可能性が示唆されています。

そのため、意図的にNULLを表現したい場合は空文字列「''」に依存せず、明示的に「NULL」キーワードを使用することが推奨されます。

OracleのNULLと0の関係は？

Oracleにおいて、NULLと数値の0は全く異なるものであり、等価ではありません。

この違いを理解することは、正確なデータ操作を行う上で非常に重要です。

なぜなら、NULLは「値の不在」を示すマーカーである一方、0は「ゼロという確定した数値」だからです。

この違いが最も顕著に現れるのが、算術式です。

NULLを含む算術式の結果は、連結演算などを除き、常にNULLに評価されます。

このように、データ集計などの際にカラムにNULLが含まれていると、SUMやAVGといった集計関数の結果に影響を与える可能性があります（多くの集計関数は計算の過程でNULLを無視します）。

したがって、NULLを0として扱いたい場合は、後述するNVL関数やCOALESCE関数を使って明示的に値を置き換える必要があります。

sqlでnull同士の比較はどうなるか

SQLにおいて、NULL同士を「=」で比較した場合、その結果はTRUEにはならず、「UNKNOWN（不明）」となります。

これは、NULLが「不明な値」を意味するため、「不明な値」と「不明な値」が等しいかどうかは判断できない、という考え方に基づいています。

この動作は「3値論理（Three-Valued Logic）」として知られています。

3値論理とは？

一般的なプログラミング言語の真偽値はTRUEかFALSEの2値ですが、SQLの論理体系はTRUE、FALSE、UNKNOWNの3つの値を持ちます。

NULLを含む比較演算は、このUNKNOWNに評価されます。

そして、SELECT文のWHERE句は、条件がTRUEと評価された行のみを返すため、UNKNOWNと評価された行は結果に含まれません。

以下のクエリを実行しても、`col1`がNULLの行は一件も返ってきません。

-- col1がNULLの行を抽出しようとする間違った例
SELECT * FROM t1 WHERE col1 = NULL;
-- 結果: 0件

NULLであるかどうかを検査するためには、専用の比較条件である「IS NULL」または「IS NOT NULL」を使用する必要があります。

-- col1がNULLの行を抽出する正しい例
SELECT * FROM t1 WHERE col1 IS NULL;

この原則は、NULLを扱う上での最も基本的なルールの一つです。

<>NULLの意味は何か

結論として、「<> NULL」や「!= NULL」といった条件式は期待通りに機能しません。

これらの比較演算子を使ってNULLと比較した場合、その結果は前述の通りTRUEやFALSEではなく、常に「UNKNOWN」となります。

「<>」は「等しくない」を意味する比較演算子ですが、比較対象の一方が「不明な値（NULL）」であるため、特定の新郎が不明な値と等しくないかどうかを判断することができません。

したがって、以下のクエリを実行してNULLでない行を抽出しようとしても、意図した結果は得られません。

-- nameがNULLでないレコードを抽出しようとする間違った例
SELECT * FROM users WHERE name <> 'taro';

このクエリでは、nameカラムがNULLのレコードは `name <> 'taro'` の評価結果がUNKNOWNになるため、抽出対象から除外されてしまいます。これが「NULLでない行が取得できない」という問題の典型的な原因です。

ある値ではないレコードを取得したい、かつNULLのレコードも取得したい場合は、以下のように「IS NOT NULL」ではなく「OR name IS NULL」を組み合わせるか、後述する関数を利用する必要があります。

-- nameが'taro'でなく、かつNULLのレコードも取得する正しい例
SELECT * FROM users WHERE name <> 'taro' OR name IS NULL;

NULLでないことを判定するには、必ず「IS NOT NULL」を使用することを徹底しましょう。

oracleにnullをセットする方法

OracleのテーブルにNULL値をセットするには、主にINSERT文またはUPDATE文で明示的に「NULL」キーワードを指定します。

INSERT文でNULLをセットする

新しいレコードを挿入する際に、特定のカラムに値を入れないことを示すためにNULLを使用します。

-- 社員テーブルに新しいレコードを挿入
-- manager_id（上司ID）が未定のためNULLをセット
INSERT INTO employees (employee_id, employee_name, manager_id)
VALUES (101, '鈴木一郎', NULL);

また、値を指定しないカラムにNOT NULL制約がなければ、そのカラムは暗黙的にNULLとなります。

-- manager_idカラムを省略すると自動的にNULLがセットされる
INSERT INTO employees (employee_id, employee_name)
VALUES (102, '佐藤花子');

UPDATE文でNULLをセットする

既存のレコードの特定のカラム値をNULLに更新する場合にも使用します。

-- 社員IDが101の社員のmanager_idをNULLに更新
UPDATE employees
SET manager_id = NULL
WHERE employee_id = 101;

NOT NULL制約に注意

当然ながら、カラムにNOT NULL制約が設定されている場合、そのカラムにNULLをセットしようとするとエラーが発生します。

テーブル設計の段階で、どのカラムがNULLを許可するか（NULLABLE）を明確にしておくことが重要です。

oracle上でnullを比較する応用テクニック

大小比較の挙動

Oracleにおいて、NULLを大小比較演算子（<, >, <=, >=）で比較した場合、その結果は常に「UNKNOWN」となります。

これは、NULLが順序や大きさを持たない「不明な値」であるため、他の値との大小関係を定義できないからです。

この仕様は、特に範囲を指定してデータを抽出する際に注意が必要です。

例えば、特定のスコア以上のユーザーを抽出しようとして、以下のSQLを実行したとします。

-- scoreが100以上のレコードを抽出しようとする例
SELECT * FROM user_scores WHERE score >= 100;

この場合、scoreカラムがNULLのレコードは、条件式 `score >= 100` の評価結果がUNKNOWNとなるため、抽出結果に含まれません。

これは「100未満」の条件 `score < 100` でも同様です。

範囲検索でのNULL漏れに注意

大小比較を行うWHERE句では、NULL値を持つレコードが暗黙的に除外されてしまうことを常に意識する必要があります。

もしNULL値を持つレコードも何らかの形で扱いたい場合は、NVL関数でデフォルト値を設定して比較するなどの工夫が求められます。

例： `WHERE NVL(score, 0) >= 100` （※ただし、この方法はパフォーマンスに影響する可能性があります）

number型のnull：判定のポイント

NUMBER型のカラムに含まれるNULL値を判定する場合も、基本原則は変わりません。

必ず「IS NULL」または「IS NOT NULL」を使用してください。

特に初心者が陥りやすい間違いとして、NUMBER型のNULLを「0」と同一視してしまうケースがあります。

前述の通り、NULLと0は全くの別物です。

以下の条件式は、異なる結果を返します。

もし、NULLと0を同じものとして扱って「0でない」レコードを抽出したい場合、以下のように比較する必要があります。

-- pointが0でない、かつNULLでもないレコードを抽出
SELECT * FROM sales WHERE point <> 0;

-- pointが0またはNULLでないレコードを抽出（これは間違い）
-- 上記のSQLではpointがNULLのレコードは抽出されません

-- 正しくは、NULLでないことを明示的に指定します
SELECT * FROM sales WHERE point <> 0 AND point IS NOT NULL;
-- もしくは単純にこう書けます
SELECT * FROM sales WHERE point <> 0;
-- しかし、0とNULLを同一視して「0でない（NULLも含む）」を抽出したい場合は注意が必要です。

もし「NULLを0とみなして比較したい」という要件がある場合は、NVL関数が非常に便利です。`NVL(point, 0)` と記述することで、pointがNULLの場合に0として評価させることができます。

例： `WHERE NVL(point, 0) <> 100`

ただし、この方法は関数を使用したカラムに対する比較となるため、そのカラムにインデックスが設定されていても利用されず、パフォーマンスが低下する可能性がある点には留意してください。

oracle is not nullが効かない原因

「IS NOT NULLが効かない」と感じる状況は、実際には「IS NOT NULL」の記述ミスではなく、NOT演算子とNULLの組み合わせによる3値論理の罠にハマっているケースがほとんどです。

最も典型的な例は、特定のステータス「以外」のデータを抽出しようとする以下のクエリです。

-- statusが'completed'でないレコードを抽出しようとする間違った例
SELECT * FROM tasks WHERE NOT (status = 'completed');

このクエリは、 `status <> 'completed'` とほぼ同義です。

ここで、statusカラムがNULLのレコードを考えてみましょう。

正しい対処法

このようなケースでは、NULLでないことを明示的に条件に加える必要があります。

方法1: IS NOT NULLを直接使う
これが最も基本的で分かりやすい方法です。

SELECT * FROM tasks WHERE status IS NOT NULL;

方法2: 条件を組み合わせる
特定の状態以外、かつNULLも対象外としたい場合。

SELECT * FROM tasks WHERE status <> 'completed' AND status IS NOT NULL;

「効かない」と感じた時は、まずクエリが3値論理の観点から正しいかどうか、特にNULLがどのように評価されるかを確認することが解決への近道です。

nullの置き換え方法

SQLクエリの実行時にNULL値を一時的に別の値に置き換えて処理したい場合、Oracleでは主に「NVL」関数と「COALESCE」関数が利用できます。

これらの関数を使うことで、NULLを含む計算や比較を安全に行えるようになります。

NVL関数

Oracle固有の関数で、2つの引数を取ります。最初の引数がNULLであれば2番目の引数の値を返し、NULLでなければ最初の引数の値そのものを返します。

-- commission_pct（歩合）がNULLの場合は0として表示
SELECT employee_name, NVL(commission_pct, 0) FROM employees;

COALESCE関数

ANSI SQL標準の関数で、複数の引数を取ることができます。引数を左から順に評価し、最初に現れたNULLでない値を返します。

-- backup_phoneがNULLならmobile_phoneを、それもNULLなら'連絡先不明'を表示
SELECT user_name, COALESCE(backup_phone, mobile_phone, '連絡先不明') FROM users;

NVLとCOALESCEの使い分け

単純にNULLを特定の一つの値に置き換えたい場合は、記述がシンプルなNVLが便利です。

複数のカラムを優先順位に従ってチェックし、最初に見つかった非NULL値を使いたい場合は、COALESCEが適しています。

COALESCEは標準SQLなので、他のデータベースへの移植性を考慮するならこちらを選択するのが良いでしょう。

sqlでnullを含む場合の対処法

前述の通り、SQLでNULLを含むデータを扱う場合、比較演算子やNOT演算子の挙動に細心の注意を払う必要があります。

NULLの存在を考慮せずにクエリを作成すると、意図したデータが取得できなかったり、逆に除外すべきデータが含まれてしまったりすることがあります。

NULLを含む可能性があるカラムを条件に指定する場合の基本的な対処法は、「NULLの場合を常に意識する」ことです。

対処法1: OR ... IS NULL を追加する

特定の条件に一致しないレコードを抽出したいが、NULLのレコードは残したい場合に有効です。

-- statusが'archived'でない、またはstatusがNULLのレコードを抽出
SELECT * FROM documents WHERE status <> 'archived' OR status IS NULL;

対処法2: IS [NOT] DISTINCT FROM を使う

この述語はNULLを安全に扱うことができます。`a IS DISTINCT FROM b` は、`a`と`b`が異なる場合にTRUEを返しますが、`=`や`<>`と違い、NULLを値として扱います。

この述語を使えば、先ほどの例はよりシンプルに記述できます。

-- statusが'archived'と異なるレコードを抽出（NULLも値として比較される）
SELECT * FROM documents WHERE status IS DISTINCT FROM 'archived';

IS DISTINCT FROMのサポートバージョン

`IS DISTINCT FROM`は便利な述語ですが、比較的新しいOracleのバージョンでサポートされた機能です。古い環境では使用できない可能性があるため、実行環境のバージョンを確認してください。

最終的な対処法として、テーブル設計の段階で不要なNULLを許可しない（NOT NULL制約を付与する）ことが、最も根本的で効果的な解決策となる場合も多いです。

oracle上でnullを比較する際の総括

この記事で解説した、OracleでNULLを比較する際の重要なポイントを以下にまとめます。

PC・家電の記事一覧へ