PostgreSQL : NULL 比較 で差がつく！ハマりやすい注意点と対策

PostgreSQLでデータベースを扱う際、NULL値の扱いは避けて通れない道です。

特に、MySQLなどの他のデータベースシステムから移行してきた場合、NULLの比較や判定で予期せぬ挙動に遭遇することがあります。

このページでは、PostgreSQLにおけるNULLの基本的な概念から、比較演算子を使ったNULLの評価、NULLを0として扱う方法、NULL値の置換などを徹底的に解説します。

これらの知識を身につけることで、PostgreSQLにおけるNULLの扱いに自信を持ち、より高度なデータベース操作をマスターすることができるでしょう。

記事のポイントです。

PostgreSQL の NULL 比較 ：MySQLとの違いとは？

PostgreSQLにおけるNULLとは？

PostgreSQLにおけるNULLとは、一言で表すと「値が存在しない」または「値が不明である」状態を示す特別な値です。

これは、数値の「0」や空文字列「""」とは本質的に異なる概念であり、データベースの世界において非常に重要な役割を担っています。

NULLは、具体的なデータ型（整数、文字列、日付など）に属するものではなく、独立した概念として存在します。

テーブル内の特定のカラムにデータがまだ入力されていない場合や、何らかの理由で値が確定できない場合に、そのカラムの値としてNULLが用いられます。

例えば、顧客データベースにおいて、まだ住所が登録されていない顧客の住所カラムはNULLとなります。

また、商品の在庫数が棚卸の結果、どうしても不明確になってしまった場合も、その商品の在庫数カラムはNULLとして扱われることがあります。

NULLの存在は、単なるデータの欠落以上の意味を持ちます。

それは、「情報が存在しない」という事実を明示的に表現する手段であり、データベースの整合性を保つ上で不可欠です。

NULLを使用することで、データベースは「0個の在庫」と「在庫数が不明」という、全く異なる状況を区別できるようになります。

これは、ビジネスロジックや意思決定において非常に重要な情報となり得ます。

SQL標準規格においては、NULLの扱いはある程度定義されていますが、具体的な実装は各データベース管理システム（DBMS）に委ねられています。

そのため、PostgreSQLを含む様々なDBMSにおいて、NULLの挙動は細部にわたって異なる場合があります。

PostgreSQLでは、NULLは他の値との比較や算術演算において特別な挙動を示すため、NULLを扱う際には常に注意が必要です。

不適切なNULLの扱いは、予期せぬエラーやデータの不整合を引き起こす可能性があります。

比較演算子を使用した場合の挙動

PostgreSQLで比較演算子（=, <, >など）をNULLに対して使用した場合、その結果はTRUEでもFALSEでもなく、常にUNKNOWN（不明）となります。

これは、NULLが「不明な値」であるため、他の値との大小関係や等価性を判断することができないという論理に基づいています。

具体例として、「SELECT 1 = NULL;」というSQLクエリを実行すると、PostgreSQLはNULL（UNKNOWN）を返します。

これは、「1は不明な値と等しいか？」という問いに対する答えが「分からない」という意味合いになります。

同様に、「SELECT 1 <> NULL;」、「SELECT 1 < NULL;」、「SELECT 1 > NULL;」といった比較演算も、すべてNULLを返します。

このNULLを返すという挙動は、WHERE句で条件を指定する際に特に重要となります。

WHERE句の条件式がUNKNOWNと評価された場合、PostgreSQLはその条件に合致する行を結果セットに含めません。

これは、UNKNOWNが「真偽が不明」であるため、条件を満たすとも満たさないとも判断できないためです。

NULLとの比較を行う場合は、IS NULL演算子またはIS NOT NULL演算子を必ず使用する必要があります。

IS NULL演算子は、値がNULLである場合にTRUEを返し、NULLでない場合にFALSEを返します。

一方、IS NOT NULL演算子は、値がNULLでない場合にTRUEを返し、NULLである場合にFALSEを返します。

これらの演算子を用いることで、NULL値の有無を明確に判定し、意図した結果を得ることができます。

算術演算子におけるNULL

PostgreSQLにおいて、算術演算子（+, -, *, /など）をNULLに対して使用すると、その結果は一律NULLとなります。

これは、NULLが「不明な値」を表すため、数値との演算結果もまた不明とならざるを得ないという論理的な帰結です。

例えば、「SELECT 1 + NULL;」というSQL文を実行した場合、PostgreSQLはNULLを返します。

これは、「1に不明な値を足すと、結果がどうなるか分からない」という状態を意味します。

同様に、「SELECT 1 - NULL;」、「SELECT 1 * NULL;」、「SELECT 1 / NULL;」といった算術演算も、すべてNULLを返します。

NULLを含む計算を行う際には、予期せぬ結果を避けるために、NULL値を適切に処理する必要があります。

そのための一般的な方法として、COALESCE関数やNULLIF関数といったSQL関数を利用することが挙げられます。

COALESCE関数は、引数として与えられた値の中で最初にNULLでない値を返します。

例えば、SELECT COALESCE(NULL, 0);というSQL文を実行すると、0が返されます。

一方、NULLIF関数は、2つの引数が等しい場合にNULLを返し、等しくない場合に最初の引数を返します。

これらの関数を適切に利用することで、NULL値を含む計算を安全に行うことができます。

ORDER BYとNULLの扱い

PostgreSQLでは、ORDER BY句を使用してクエリ結果をソートする際、NULL値をどのように扱うかを指定することができます。

PostgreSQLのデフォルトの動作では、NULLは他のすべての非NULL値よりも大きいとみなされます。

そのため、昇順（ASC）でソートした場合、NULL値を持つ行は結果セットの最後に配置され、降順（DESC）でソートした場合は、NULL値を持つ行は結果セットの最初に配置されます。

しかし、このデフォルトの動作は、必ずしもすべての状況において適切であるとは限りません。

例えば、NULL値を常に最初に表示したい場合や、NULL値を常に最後に表示したい場合があります。

PostgreSQLでは、このようなニーズに対応するために、NULLS FIRSTオプションとNULLS LASTオプションが用意されています。

NULLS FIRSTオプションを使用すると、ソート順に関わらず、NULL値を持つ行を常に最初に配置することができます。

例えば、SELECT * FROM users ORDER BY name ASC NULLS FIRST;というSQL文を実行すると、nameカラムの値で昇順にソートされ、NULL値を持つ行が最初に表示されます。

一方、NULLS LASTオプションを使用すると、ソート順に関わらず、NULL値を持つ行を常に最後に配置することができます。

例えば、SELECT * FROM users ORDER BY name DESC NULLS LAST;というSQL文を実行すると、nameカラムの値で降順にソートされ、NULL値を持つ行が最後に表示されます。

これらのオプションを適切に使用することで、NULL値の表示順序を柔軟に制御し、意図した結果を得ることができます。

結果をTRUE/FALSEとして扱いたい場合

PostgreSQLでNULLとの比較結果をTRUEまたはFALSEとして明確に扱いたい場合、標準SQLで提供されているIS DISTINCT FROM演算子とIS NOT DISTINCT FROM演算子が非常に有効です。

これらの演算子は、NULL値を単なる不明な値としてではなく、比較可能な値として扱うことを可能にします。

IS DISTINCT FROM演算子は、2つの値が互いに異なる場合にTRUEを返し、同じ場合にFALSEを返します。

特に重要なのは、NULL同士を比較した場合にもTRUEを返す点です。

これは、NULLが他のどの値とも異なるとみなされるためです。

例えば、SELECT NULL IS DISTINCT FROM NULL;というSQL文はFALSEを返します。

一方、SELECT 1 IS DISTINCT FROM NULL;というSQL文はTRUEを返します。

IS NOT DISTINCT FROM演算子は、IS DISTINCT FROM演算子の反対の動作をします。

つまり、2つの値が同じ場合にTRUEを返し、異なる場合にFALSEを返します。

NULL同士を比較した場合にもTRUEを返すため、NULL値の一致を判定する際に役立ちます。

例えば、SELECT NULL IS NOT DISTINCT FROM NULL;というSQL文はTRUEを返します。

一方、SELECT 1 IS NOT DISTINCT FROM NULL;というSQL文はFALSEを返します。

これらの演算子は、特に複雑な条件を持つクエリや、NULL値の有無によって処理を分岐させたい場合に非常に有用です。

例えば、あるカラムの値が特定の値と異なるすべての行（NULLを含む）を抽出したい場合に、IS DISTINCT FROM演算子を使用することで、簡潔かつ正確なクエリを記述することができます。

SQLの比較演算子で<>と!=の違いは何ですか？

SQLにおいて、<>演算子と!=演算子は、どちらも「等しくない」という比較を行うために使用されます。

しかし、これらの演算子の間には、いくつかの重要な違いが存在します。

SQLの標準規格（ANSI SQL）においては、<>演算子が「等しくない」を意味する標準的な演算子として定義されています。

一方、!=演算子は、SQL標準には含まれておらず、一部のデータベース管理システム（DBMS）において独自に拡張された演算子です。

PostgreSQLを含む多くのDBMSでは、!=演算子は<>演算子と完全に同じ意味で解釈され、内部的には<>演算子に変換されて処理されます。

したがって、PostgreSQLにおいては、!=演算子を使用しても<>演算子を使用しても、結果は全く同じになります。

しかし、SQLの移植性を考慮すると、<>演算子を使用することが推奨されます。

なぜなら、<>演算子はSQL標準に準拠しているため、様々なDBMSで一貫して動作することが保証されているからです。

一方、!=演算子は、SQL標準ではないため、DBMSによってはサポートされていない場合や、異なる意味を持つ場合があります。

したがって、特に複数のDBMSを使用する可能性がある場合は、<>演算子を使用することで、SQLの移植性を高め、予期せぬエラーを回避することができます。

NULLとfalseはどう違うのか？

NULLとfalseは、プログラミングやデータベースの世界において、しばしば混同されがちな概念です。

しかし、これらの概念は本質的に異なり、それぞれ異なる意味と役割を持っています。

NULLは、値が存在しない、あるいは値が不明であることを示す特別な値です。

これは、数値の「0」や空文字列「""」とは異なり、具体的な値を持たない状態を表します。

NULLは、データベースのテーブルにおいて、特定のカラムにデータがまだ入力されていない場合や、何らかの理由で値が確定できない場合に用いられます。

一方、falseは、論理的な偽（ぎ）を表すブール値です。

ブール値は、TRUE（真）またはFALSE（偽）のいずれかの値を取り、条件の成立・不成立や、何らかの命題の真偽を表すために使用されます。

NULLは、データの欠損や不明確さを表現するために使用されるのに対し、falseは、明確な「偽」の状態を表すために使用されます。

例えば、顧客データベースにおいて、まだ電話番号が登録されていない顧客の電話番号カラムはNULLとなります。

一方、その顧客がアクティブな会員であるかどうかを示すフラグには、FALSEが使用されることがあります。

NULLは、他の値との比較や演算において特別な挙動を示すため、falseとは異なる扱いが必要です。

NULLとの比較は常にUNKNOWNとなり、算術演算の結果もNULLとなります。

一方、falseは、論理演算（AND、OR、NOTなど）で使用され、TRUEと組み合わせて複雑な条件を表現することができます。

したがって、NULLとfalseは、それぞれ異なる目的で使用され、異なる性質を持つ概念であることを理解することが重要です。

【 比較 】PostgreSQL で NULL を扱う際の注意点と対策

null判定 でハマらないために

PostgreSQLでNULL判定を行う際、多くの開発者が一度は経験するのが、予期せぬ結果に直面してしまうという落とし穴です。

NULLは、他の値とは異なる特別な性質を持つため、通常の比較演算子(=, <>, >, <など)を使用しても期待通りの結果は得られません。

この罠にハマらないためには、NULLの特性を正しく理解し、適切なNULL判定方法を習得することが不可欠です。

まず、比較演算子をNULLに対して使用した場合、結果はTRUEでもFALSEでもなく、UNKNOWN(不明)となることを覚えておきましょう。

例えば、SELECT * FROM users WHERE name = NULL;というクエリは、一見するとnameカラムがNULLであるすべての行を抽出できそうに思えますが、実際には何も返しません。

これは、name = NULLという条件がUNKNOWNと評価されるため、WHERE句の条件を満たす行が存在しないと判断されるためです。

では、どのようにすればNULL判定を正しく行えるのでしょうか？

その答えは、IS NULL演算子とIS NOT NULL演算子を使用することです。

IS NULL演算子は、値がNULLである場合にTRUEを返し、NULLでない場合にFALSEを返します。

一方、IS NOT NULL演算子は、値がNULLでない場合にTRUEを返し、NULLである場合にFALSEを返します。

したがって、nameカラムがNULLであるすべての行を抽出するには、SELECT * FROM users WHERE name IS NULL;というクエリを使用する必要があります。

また、nameカラムがNULLでないすべての行を抽出するには、SELECT * FROM users WHERE name IS NOT NULL;というクエリを使用します。

これらのIS NULL演算子とIS NOT NULL演算子を適切に使用することで、NULL判定に関する多くの問題を回避し、より正確で信頼性の高いSQLクエリを作成することができます。

where：null以外を抽出する方法

PostgreSQLでNULL以外の値を持つ行を抽出する場合、WHERE句とIS NOT NULL演算子を組み合わせるのが基本です。

IS NOT NULL演算子は、指定されたカラムの値がNULLでない場合にTRUEを返し、NULLである場合にFALSEを返します。

この特性を利用することで、簡単にNULL以外の値を持つ行を抽出できます。

例えば、usersテーブルのemailカラムにNULL以外の値が設定されている行を抽出するには、以下のクエリを実行します。

SELECT * FROM users WHERE email IS NOT NULL;

このクエリは、emailカラムの値がNULLでないすべての行を返します。

さらに、特定の条件を満たすNULL以外の値を持つ行を抽出することも可能です。

例えば、usersテーブルのemailカラムにNULL以外の値が設定されており、かつactiveカラムの値がTRUEである行を抽出するには、以下のクエリを実行します。

SELECT * FROM users WHERE email IS NOT NULL AND active = TRUE;

このクエリは、emailカラムの値がNULLでなく、かつactiveカラムの値がTRUEであるすべての行を返します。

IS NOT NULL演算子とWHERE句を組み合わせることで、さまざまな条件に基づいてNULL以外の値を持つ行を柔軟に抽出することができます。

空文字とNULLの違いは？

PostgreSQLにおいて、空文字("")とNULLは、一見似ているように見えますが、実際には全く異なる概念です。

これらの違いを理解することは、データベースの設計やクエリの作成において非常に重要です。

空文字("")は、長さが0の文字列であり、有効な値として扱われます。

つまり、空文字は「文字列型の値が存在するが、その内容が空である」という状態を表します。

一方、NULLは、値が存在しない、あるいは値が不明であることを示す特別な値です。

NULLは、具体的なデータ型に属するものではなく、独立した概念として存在します。

これらの違いを明確にするために、具体的な例を考えてみましょう。

例えば、usersテーブルのaddressカラムに空文字("")が設定されている場合、それは「住所は存在するが、その内容は空である」ということを意味します。

一方、addressカラムにNULLが設定されている場合、それは「住所が存在しない」あるいは「住所が不明である」ということを意味します。

空文字とNULLは、比較演算子による評価も異なります。

空文字は、他の文字列と比較することができます。

例えば、SELECT '' = 'abc';というクエリはFALSEを返します。

一方、NULLは、比較演算子(=, <>, >, <など)を使用して他の値と比較することはできません。

NULLとの比較は常にUNKNOWNとなります。

NULLを比較するには、IS NULL演算子またはIS NOT NULL演算子を使用する必要があります。

したがって、空文字とNULLは、それぞれ異なる意味を持ち、異なる方法で処理する必要があることを覚えておきましょう。

nullを入れる時の注意点

PostgreSQLにNULL値を挿入する際には、いくつかの注意点があります。

これらの注意点を守ることで、データの整合性を保ち、予期せぬエラーを回避することができます。

まず、カラムがNULL値を許可するように定義されていることを確認してください。

テーブル定義において、カラムにNOT NULL制約が設定されている場合、そのカラムにNULL値を挿入しようとするとエラーが発生します。

カラムがNULL値を許可するように定義するには、テーブル作成時または変更時にNOT NULL制約を設定しないでおく必要があります。

次に、NULL値を挿入する際には、NULLキーワードを使用してください。

例えば、usersテーブルのemailカラムにNULL値を挿入するには、以下のクエリを実行します。

INSERT INTO users (name, email) VALUES ('John Doe', NULL);

このクエリは、nameカラムに'John Doe'を、emailカラムにNULLを挿入します。

また、NULL値を挿入する際には、データ型に注意する必要があります。

例えば、整数型のカラムにNULL値を挿入する場合、NULLキーワードをそのまま使用できます。

しかし、文字列型のカラムにNULL値を挿入する場合は、NULLキーワードをシングルクォートで囲む必要はありません。

'NULL'という文字列を挿入してしまうと、それはNULL値ではなく、単なる文字列として扱われてしまいます。

さらに、外部キー制約が設定されているカラムにNULL値を挿入する場合は、参照先のテーブルに一致する値が存在する必要はありません。

外部キー制約は、参照整合性を保つために、参照元のテーブルに存在する値のみを参照できるようにするものですが、NULL値は例外として扱われます。

これらの注意点を守ることで、PostgreSQLにNULL値を安全かつ正確に挿入することができます。

COALESCE関数で値を置換

PostgreSQLにおいて、COALESCE関数は、NULL値を別の特定の値で置き換えるための非常に強力かつ便利なツールです。

この関数を使用することで、NULL値によって引き起こされる可能性のある問題を回避し、データ分析やレポート作成をよりスムーズに行うことができます。

COALESCE関数は、複数の引数を取ることができ、引数のリストの中で最初にNULLでない値を返します。

この特性を利用して、あるカラムの値がNULLである場合に、別のデフォルト値や代替値を返すように設定することができます。

例えば、製品テーブル(products)にdiscount_priceというカラムがあり、割引価格が設定されていない場合はNULLになっているとします。

この場合、製品の価格を表示する際に、割引価格が設定されていれば割引価格を、設定されていなければ通常の価格を表示したいというニーズがあるとします。

このような場合に、COALESCE関数を使用すると、以下のようなクエリで簡単に実現できます。

SELECT
    product_name,
    COALESCE(discount_price, regular_price) AS actual_price
FROM
    products;

このクエリでは、discount_priceがNULLでない場合はdiscount_priceの値が、NULLの場合はregular_priceの値がactual_priceとして表示されます。

このように、COALESCE関数を使用することで、NULL値を柔軟に処理し、データの見やすさや使いやすさを向上させることができます。

nullの場合0に変換する

PostgreSQLで、NULL値を数値の0に変換したい場合、COALESCE関数が非常に有効です。

これは、集計関数(SUM, AVGなど)を使用する際に、NULL値が計算結果に影響を与えないようにするためによく行われる処理です。

例えば、売上テーブル(sales)にamountというカラムがあり、一部のレコードでは売上額がNULLになっているとします。

この場合、全体の売上合計を計算する際に、NULL値を0として扱うことで、正確な合計値を算出することができます。

COALESCE関数を使用すると、以下のようなクエリで簡単にNULL値を0に変換できます。

SELECT SUM(COALESCE(amount, 0)) AS total_sales FROM sales;

このクエリでは、amountカラムがNULLの場合は0として扱われ、その合計がtotal_salesとして計算されます。

COALESCE関数は、0以外の値でNULLを置き換えたい場合にも使用できます。

例えば、在庫テーブル(inventory)にquantityというカラムがあり、在庫数がNULLの場合はデフォルト値として10を設定したい場合は、以下のようなクエリを使用します。

SELECT COALESCE(quantity, 10) AS available_quantity FROM inventory;

このように、COALESCE関数を使用することで、NULL値を柔軟に処理し、データの特性や要件に合わせて適切な値に変換することができます。

transform_null_equalsパラメータ

transform_null_equalsパラメータは、PostgreSQLの高度な設定オプションの一つであり、NULL値の比較に関する挙動を制御するために使用されます。

デフォルトでは、PostgreSQLにおいてexpression = NULLという比較は常にUNKNOWN(NULL)を返します。

しかし、transform_null_equalsパラメータをonに設定すると、expression = NULLという比較はexpression IS NULLという比較に変換されます。

これは、一部のアプリケーションやフレームワークが、NULL値の比較に=演算子を使用することを前提としている場合に役立ちます。

ただし、このパラメータを使用する際には、注意が必要です。

transform_null_equalsをonに設定すると、SQL標準からの逸脱が発生する可能性があります。

また、=演算子を使用したNULL値の比較が、意図しない結果を引き起こす可能性もあります。

したがって、transform_null_equalsパラメータを使用する際には、その影響を十分に理解し、慎重に検討する必要があります。

-- transform_null_equalsをonに設定
SET transform_null_equals = on;

-- NULL = NULLの比較がTRUEを返すようになる
SELECT NULL = NULL; -- 結果: t (TRUE)

-- transform_null_equalsをoffに戻す
SET transform_null_equals = off;

-- NULL = NULLの比較は再びNULLを返す
SELECT NULL = NULL; -- 結果: (NULL)

nullにする方法

PostgreSQLでカラムの値をNULLにする方法はいくつか存在します。

最も一般的な方法は、UPDATEステートメントを使用することです。

UPDATEステートメントを使用すると、特定の行の特定のカラムの値をNULLに設定することができます。

例えば、usersテーブルのemailカラムの値を、特定のユーザーの行でNULLにするには、以下のクエリを実行します。

UPDATE users SET email = NULL WHERE user_id = 123;

このクエリは、user_idが123であるユーザーのemailカラムの値をNULLに設定します。

また、INSERTステートメントを使用して新しい行を挿入する際に、特定のカラムの値をNULLにすることもできます。

例えば、ordersテーブルに新しい注文を挿入する際に、shipping_addressカラムの値をNULLにするには、以下のクエリを実行します。

INSERT INTO orders (order_id, customer_id, shipping_address) VALUES (456, 789, NULL);

このクエリは、order_idが456、customer_idが789であり、shipping_addressがNULLである新しい行をordersテーブルに挿入します。

さらに、ALTER TABLEステートメントを使用して、カラムのデフォルト値をNULLに設定することもできます。

カラムのデフォルト値をNULLに設定すると、新しい行を挿入する際に、そのカラムの値を明示的に指定しない場合、自動的にNULLが挿入されます。

ALTER TABLE products ALTER COLUMN description SET DEFAULT NULL;

このコマンドを実行すると、productsテーブルのdescriptionカラムのデフォルト値がNULLに設定されます。

これらの方法を適切に使い分けることで、PostgreSQLでカラムの値をNULLに設定することができます。

PostgreSQL の NULL 比較 のまとめ

次のように記事の内容をまとめました。

• NULLは「値が存在しない」または「値が不明」な状態を示す特別な値である
• NULLは具体的なデータ型に属さず、独立した概念である
• PostgreSQLでは、NULLは他の値との比較で常にUNKNOWNを返す
• WHERE句でNULLを比較するには、IS NULLまたはIS NOT NULL演算子を使用する
• 算術演算子をNULLに適用すると、結果は常にNULLとなる
• COALESCE関数は、NULL値を別の値に置き換えるために使用できる
• ORDER BY句でNULLの扱いを指定するには、NULLS FIRSTまたはNULLS LASTオプションを使用する
• IS DISTINCT FROM演算子は、NULL同士を比較した場合にFALSEを返す
• IS NOT DISTINCT FROM演算子は、NULL同士を比較した場合にTRUEを返す
• SQL標準では、<>演算子が「等しくない」を意味する標準的な演算子である
• 空文字("")は、長さが0の文字列であり、有効な値である
• カラムがNULL値を許可するように定義されていることを確認する必要がある
• transform_null_equalsパラメータは、NULL値の比較に関する挙動を制御する
• UPDATEステートメントを使用すると、カラムの値をNULLに設定できる
• NULLとfalseは異なる概念であり、それぞれ異なる意味と役割を持つ