【SQL】NULL同士がJOINで一致しない理由とは？結合が失敗する理由と正しい書き方（PostgreSQL）

はじめに

SQLを実行するとき、カラムにNULLが入っているとしばしば意図しない挙動をしてしまうことがあります。
この記事では、SQLにおいてNULLがどのような挙動をするのか、どのようにNULLを制御できるのかをPostgreSQLを用いて解説します。

NULLを考慮していないJOIN

具体例を見てみましょう。以下の2つのテーブルから製品名とバージョンの組み合わせごとに売り上げ金額を算出したい場合、どのようなSQLを作ることになるでしょうか。

製品マスター

売り上げデータ

SELECT              
    売り上げデータ.製品名,
    売り上げデータ.バージョン,
    売り上げデータ.売り上げ個数,
    製品マスター.単価,
    売り上げデータ.売り上げ個数 * 製品マスター.単価 AS 売り上げ金額
FROM 売り上げデータ
JOIN 製品マスター
  ON 製品マスター.製品名 = 売り上げデータ.製品名
 AND 製品マスター.バージョン = 売り上げデータ.バージョン;

このようにJOINの条件で単純にカラムを「=」で繋ぐと、以下の通り鏡の売り上げを出力することができません。

原因はNULLが悪さをしているからです。
鏡の売り上げも含めて売り上げを出したい場合、正しくは以下のようなSQLになります。

SELECT
    売り上げデータ.製品名,
    売り上げデータ.バージョン,
    売り上げデータ.売り上げ個数,
    製品マスター.単価,
    売り上げデータ.売り上げ個数 * 製品マスター.単価 AS 売り上げ金額
FROM 売り上げデータ
JOIN 製品マスター
  ON 製品マスター.製品名 = 売り上げデータ.製品名
 AND 製品マスター.バージョン IS NOT DISTINCT FROM 売り上げデータ.バージョン;

この記事では、なぜNULLが含まれる場合にこのようなSQLにする必要があるのか、SQLにおけるNULLについて解説します。

NULLはSQLの様々な演算で用いることができますが、演算の種類によって大きく2種類の振る舞いをすると捉えると理解しやすいです。次章からそれぞれを解説します。

算術演算でのNULLの振る舞い

NULLは値ではなく、"値が欠落している状態"を表します。
そのため、算術演算で数値を計算すると

 select 1 + 1;
-- 出力: 2
select 2 ^ 3;
-- 出力: 8

このように演算が行われますが、算術演算の演算対象にNULLが含まれていると

select 1 + NULL;
-- 出力: NULL
select 2 ^ NULL;
-- 出力: NULL

出力結果はNULLになります。値が欠落しており通常の算数のように計算ができないためこのような挙動になります。
複雑な算術演算式を組んだとして、演算対象に1つでもNULLが含まれていると結果がNULLになってしまうことを「NULLが伝播する」と表現します。
算術演算を実装する場合、演算対象がNULLになり得る場合は結果がNULLで潰れてしまうことになりかねないので注意して設計することが必要になります。

比較演算/論理演算でのNULLの振る舞い

比較演算では値同士を比較するとtrueもしくはfalseが出力されます。

select 1 = 1;
-- 出力: TRUE
select 1 > 2;
-- 出力: FALSE

ただ、演算対象にNULLが含まれていた場合、算術演算と同じくその結果もNULLになります。

select 1 = NULL;
-- 出力: NULL
select 1 > NULL;
-- 出力: NULL

一般的なプログラミング言語ではこのような計算をするとfalseが出力されるかエラーになるでしょう。なぜSQLではfalseを出力するでもエラーになるでもなくNULLが出力されるかというと、SQLの論理演算はtrue/falseの2つの値で評価する2値論理ではなく、true/falseに加えてunknownという3つ目の値を含んでいる3値論理だからです。
unknownとは、意味的にはtrueかfalseかが決まらない未知の状態、もしくはtrue/falseで評価できないような状態を表します。例えば「明日は雨が降る」という文は、明日にならないと真偽がわからないため未知の状態と言えます。また、「人間の尻尾の長さ」という概念はそもそも人間に尻尾が生えていないので無意味な概念です。このようなものはtrue/falseでは評価できません。このような未知の状態、true/falseで評価できない状態を3値論理では"unknown"と呼びます。
SQLのBOOLEAN型はこの3値論理を踏襲していますが、前章でNULLは"値が欠落している状態"を指すと説明した通り、3値論理のunknownはSQLでは"NULL"で表現されます。
比較演算では演算対象にNULLが含まれていると、演算結果はunknownとなる、つまりNULLが出力されることになります。

算術演算や比較演算では演算対象にNULLが含まれていれば結果もNULLになりましたが、論理演算では挙動が少し複雑になります。
SQLにおける論理演算が従う3値論理のルールは以下の通りです。

※SQLではunknownはNULLで表されます。

通常のプログラミング言語で扱うような論理演算（2値論理）でもAND演算ではfalseが強く（falseが一つでも入っていれば結果がfalseになる）、OR演算ではtrueが強いですが、AND演算においてもOR演算においても、unknownはfalseとtrueの中間の強さを持ちます。

SELECT (1 = 1) OR (NULL = 1);
-- 出力: TRUE

SELECT (1 <> 1) OR (NULL = 1);
-- 出力: NULL

NULLを考慮したJOIN

冒頭の話に戻って、製品マスターと売り上げデータをJOINして、鏡の売り上げも含めて金額を算出するにはどうすればよいでしょうか。

製品マスター

売り上げデータ

先ほど説明した通り「NULL=NULL」という式では値が欠落した状態を比較することができないので、NULLを返してしまいます。
そこで、NULL同士も等しいとみなして比較できる特殊な比較演算子「IS NOT DISTINCT FROM」を使う必要があります。

SELECT              
    売り上げデータ.製品名,
    売り上げデータ.バージョン,
    売り上げデータ.売り上げ個数,
    製品マスター.単価,
    売り上げデータ.売り上げ個数 * 製品マスター.単価 AS 売り上げ金額
FROM 売り上げデータ
JOIN 製品マスター
  ON 製品マスター.製品名 = 売り上げデータ.製品名
 AND 製品マスター.バージョン = 売り上げデータ.バージョン;

このJOIN句の2つ目の条件式

製品マスター.バージョン = 売り上げデータ.バージョン

こちらを

製品マスター.バージョン IS NOT DISTINCT FROM 売り上げデータ.バージョン

に置き換えることで以下のように鏡を含めて計算ができます。

SELECT
    売り上げデータ.製品名,
    売り上げデータ.バージョン,
    売り上げデータ.売り上げ個数,
    製品マスター.単価,
    売り上げデータ.売り上げ個数 * 製品マスター.単価 AS 売り上げ金額
FROM 売り上げデータ
JOIN 製品マスター
  ON 製品マスター.製品名 = 売り上げデータ.製品名
 AND 製品マスター.バージョン IS NOT DISTINCT FROM 売り上げデータ.バージョン;

「IS NOT DISTINCT FROM」は単語数が多く、一見複雑な構文を書いているのではと思ってしまいますが、実際にはこの単語の並びがひとまとまりで「=」のような1つの比較演算子の役割を果たしています。

また、COALESCE関数を使用することでもNULLを考慮したJOINが実現できます。
COALESCE関数は、与えられた引数のうちNULLでない最初の引数を返してくれます。

COALESCE(A, B, C)

このようなSQLがあるとして、AがNULLではない場合はAを返し、AがNULLでBがNULLではない場合はBを、AとBがNULLだった場合はCを返します。引数全てNULLの場合はNULLを返します。
この関数を使ってバージョンがNULLだった場合に-1に変換させることで、NULLが入ったカラムを値として扱うことが可能です。

SELECT
    売り上げデータ.製品名,
    売り上げデータ.バージョン,
    売り上げデータ.売り上げ個数,
    製品マスター.単価,
    売り上げデータ.売り上げ個数 * 製品マスター.単価 AS 売り上げ金額
FROM 売り上げデータ
JOIN 製品マスター
  ON 製品マスター.製品名 = 売り上げデータ.製品名
 AND COALESCE(製品マスター.バージョン, -1) = COALESCE(売り上げデータ.バージョン, -1);

※ ただし、この方法ではインデックスが使用されない場合があり、またテーブル内に実際に-1という値が存在する場合は誤った結合が発生する可能性があります。そのため、可能であれば IS NOT DISTINCT FROM の使用が推奨されます。

まとめ

以上、SQLにおけるNULLの扱いについて解説しました。
通常のプログラミング言語とは異なりSQLのNULLは独特の挙動をします。
特にJOINを行う際は各カラムにNULLが許容されているのかを確認し、NULL同士が通常の「=」では一致しないことを理解した上でクエリを作る必要があります。
WHERE句や集計処理においてもNULLは結果に大きく影響するため、「NULLは値ではなく欠落を表す状態である」という前提を常に意識することが重要です。