SQL練習室
← SQLガイド

SQLのIS DISTINCT FROMとは?NULLを値として比べる方法と<>との違い

IS DISTINCT FROMは、NULLを「ひとつの値」として扱って2つの値が異なるかを調べる比較です。<>はどちらかがNULLだと結果がNULLになり、WHEREで行が落ちますが、IS DISTINCT FROMは必ずtrueかfalseを返します。旧値と新値の変更検知を中心に、<>との違いを確認します。

例で使う表

product_pricesは1行が商品1件で、old_priceは旧価格、new_priceは新価格(円)です。NULLは価格未設定を表します。ペンは同じ価格、ノートは値上げ、定規は両方NULL、付箋は新たに設定、のりは設定を取り消し、クリップは旧価格0円から未設定になっています。membersは会員、areasは地域コードと名前の一覧で、areasにはコードがNULLの「未設定」の行があります。

product_prices:商品6件
idproductold_pricenew_price
1ペン100100
2ノート200250
3定規NULLNULL
4付箋NULL150
5のり300NULL
6クリップ0NULL
members:会員4人
idnamearea_code
1青木A
2伊藤B
3上田NULL
4江口NULL
areas:地域3件
codelabel
A東日本
B西日本
NULL未設定

IS DISTINCT FROMの基本構文

sql
SELECT id, product
FROM product_prices
WHERE old_price IS DISTINCT FROM new_price
ORDER BY id;

IS DISTINCT FROMは異なるかを必ずtrueかfalseで返す

a IS DISTINCT FROM bは、aとbが異なればtrue、同じならfalseを返します。普通の<>と違うのは、NULLを1つの値として扱う点です。NULLと数値は「異なる」のでtrue、NULLとNULLは「同じ」なのでfalseになり、結果がNULLになることはありません。ペン(100と100)は同じなのでfalse、ノート(200と250)はtrue、定規(NULLとNULL)はfalse、付箋・のり・クリップは片方がNULLなのでtrueです。

sql
SELECT id, product, old_price IS DISTINCT FROM new_price AS changed
FROM product_prices
ORDER BY id;
実行結果
idproductchanged
1ペンfalse
2ノートtrue
3定規false
4付箋true
5のりtrue
6クリップtrue

<>はNULLが絡むと行が落ちる

old_price <> new_priceは、どちらかがNULLだと結果がtrueでもfalseでもなくNULLになります。WHEREはtrueの行だけを残すので、NULLの行はすべて落ちます。結果は両方に値があって違うノートの1行だけで、付箋・のり・クリップの変更が見つかりません。エラーにはならないため、変更が漏れていても気づきにくい点に注意してください。

sql
SELECT *
FROM product_prices
WHERE old_price <> new_price
ORDER BY id;
実行結果
idproductold_pricenew_price
2ノート200250

IS DISTINCT FROMで旧値と新値が変わった行を拾う(変更検知)

同じ条件を<>の代わりにIS DISTINCT FROMにすると、価格が変わった行がすべて残ります。ノート、付箋(新たに設定)、のり(設定を取り消し)、クリップ(0円から未設定)の4行です。ペンと、両方NULLの定規は変わっていないので入りません。旧値と新値を比べる変更検知や差分チェックで、NULLが入りうる列を比べるときの基本形です。

sql
SELECT *
FROM product_prices
WHERE old_price IS DISTINCT FROM new_price
ORDER BY id;
実行結果
idproductold_pricenew_price
2ノート200250
4付箋NULL150
5のり300NULL
6クリップ0NULL

IS NOT DISTINCT FROMはNULLどうしを等しいとみなす

IS NOT DISTINCT FROMは、IS DISTINCT FROMの反対で、同じならtrueです。=と違い、NULLとNULLも「同じ」としてtrueを返します。価格が変わっていない行を探すと、ペン(100と100)と定規(NULLとNULL)の2行が残ります。old_price = new_priceで探すとペンしか残らず、両方NULLの定規が「変更なし」として拾えません。

sql
SELECT *
FROM product_prices
WHERE old_price IS NOT DISTINCT FROM new_price
ORDER BY id;
実行結果
idproductold_pricenew_price
1ペン100100
3定規NULLNULL

定数との比較でも、NULLの行を落とさずに「異なる」を判定する

比較の相手は列だけでなく定数でもかまいません。new_price <> 150だと、new_priceがNULLの定規・のり・クリップは落ちて、ペンとノートの2行しか残りません。new_price IS DISTINCT FROM 150にすると、150円ではない行として、NULLの行も残ります。結果は、ペン、ノート、定規、のり、クリップの5行です。付箋は150円なので入りません。「その値以外(未設定を含む)」を取りたい場面で使います。

sql
SELECT id, product, new_price
FROM product_prices
WHERE new_price IS DISTINCT FROM 150
ORDER BY id;
実行結果
idproductnew_price
1ペン100
2ノート250
3定規NULL
5のりNULL
6クリップNULL

結合条件でNULLどうしを対応させる

JOINのONで=を使うと、NULLどうしは対応せず、area_codeがNULLの上田と江口は結果から消えます。結果は青木と伊藤の2行です。ONにIS NOT DISTINCT FROMを使うと、NULLのareas行(未設定)と対応するので、上田と江口が「未設定」に結び付き、4行になります。NULLを意味のある値(未設定)として別表に持っている場合だけ使います。通常のコード(A、B)は、どちらの書き方でも同じように対応します。

sql
SELECT members.name, areas.label
FROM members
JOIN areas ON members.area_code IS NOT DISTINCT FROM areas.code
ORDER BY members.id;
実行結果
namelabel
青木東日本
伊藤西日本
上田未設定
江口未設定

IS DISTINCT FROMでよくある間違い

COALESCEで0などに置き換えて比べると、実在する値と区別できない

COALESCE(old_price, 0) <> COALESCE(new_price, 0)のように、NULLを0へ置き換えて<>で比べる書き方があります。しかしクリップのように旧価格が本当に0円で、新価格が未設定の行では、どちらも0になり「変更なし」と判定されます。置き換える値が実際のデータにも存在しうるときは、IS DISTINCT FROMを使ってください。この表では、変更のあるクリップ(id 6)が結果から落ちます。

sql
SELECT id, product
FROM product_prices
WHERE COALESCE(old_price, 0) <> COALESCE(new_price, 0)
ORDER BY id;

実際に1問解いてみる

IS DISTINCT FROM

商品(product_prices)ごとに、idと、旧価格と新価格が異なるかどうかの真偽値をchangedという列名で取得してください。NULLは1つの値として扱います。idの昇順に並べます。

テーブル

読み込み中…

実行結果

関連構文

IS DISTINCT FROMについてよくある質問

<>とIS DISTINCT FROMの違いは何ですか?
<>は、どちらかがNULLだと結果がNULLになり、WHEREで行が落ちます。IS DISTINCT FROMはNULLを値として扱い、NULLと値は異なる、NULLどうしは同じとして、必ずtrueかfalseを返します。この表では、old_price <> new_priceが1行、IS DISTINCT FROMが4行です。
NULLとの比較は、IS NULLと= NULLのどちらで書きますか?
= NULLや<> NULLは常にNULLとなり、WHEREでは行が残りません。NULLかどうかはIS NULL、IS NOT NULLで調べます。2つの列をNULLを含めて比べるときに、IS DISTINCT FROMとIS NOT DISTINCT FROMを使います。
IS DISTINCT FROMは他のデータベースでも使えますか?
標準SQLにある比較述語ですが、対応状況や書き方はデータベースごとに異なります。たとえばMySQLには、IS NOT DISTINCT FROMと同じ働きをする<=>演算子があります。この記事の書き方はPostgreSQLのものです。
IS DISTINCT FROMとIS NOT DISTINCT FROMはどう使い分けますか?
「異なる行(変更のある行)」を取りたいときはIS DISTINCT FROM、「同じ行(変更のない行)」を取りたいときや、NULLどうしを対応させたいJOINにはIS NOT DISTINCT FROMを使います。どちらも、一方を否定すると、もう一方と同じ結果になります。

参考文献

内容はPostgreSQL公式ドキュメントを参照し、初めて学ぶ人向けに日本語で整理しています。