SQL・データベース
PR

SQLのNULLの扱い方|IS NULL・COALESCE・集計と比較の落とし穴

SQLのNULLの扱い方|IS NULL・COALESCE・集計と比較の落とし穴
hr
記事内に商品プロモーションを含む場合があります

SQL の NULL(ヌル)は「値がない」「分からない」ことを表す特別な印です。WHERE price = NULL が 0 行になる、計算結果がいつの間にか NULL になる、NOT IN が何も返さない……といった、エラーにならないのに結果がおかしいトラブルの多くは NULL が原因です。

この記事では、練習用の会員テーブルで実際に実行した結果を見ながら、NULL と空文字・0 の違い、3 値論理(TRUE・FALSE・UNKNOWN)、IS NULL、計算と文字列の連結、COALESCE・NULLIF、集計・並べ替え・NOT IN・UNIQUE 制約での NULL の扱い、NULL どうしを比べる IS DISTINCT FROM を解説します。SELECT 文の基本は「SQL入門:SELECT文の基本」で解説しています。

NULL とは:空文字や 0 とは別のもの

NULL は「その欄に値が入っていない」状態です。MySQL の公式マニュアルでは「概念としては、NULL は値が欠けていて分からないことを意味する」と説明されています。よく混同される空文字('')や 0 は、どちらもれっきとした値です。

入っているもの意味の例(ニックネーム・ポイント)IS NULL
NULLまだ入力していない・分からない真
空文字 ''「ニックネームなし」と入力した(長さ 0 の文字列)偽
0ポイントが 0 点だと分かっている偽

「ポイント 0 点」と「ポイント不明」は意味が違います。NULL を 0 の代わりに使ったり、0 を NULL の代わりに使ったりすると、合計や平均の結果が変わってしまいます。なお Oracle Database は長さ 0 の文字列を NULL として扱いますが、Oracle の公式ドキュメント自身が「将来は変わるかもしれないので、空文字を NULL と同じように扱わないこと」と勧めています。

練習用のテーブルを用意する

Python に標準で入っている SQLite(エスキューライト)を使います。次のコードを sql_null_practice.py という名前で保存してください。会員(members)テーブルをメモリー上に作ります。データは終了すると消えます。

import sqlite3

con = sqlite3.connect(":memory:")
con.executescript("""
CREATE TABLE members (
  id          INTEGER PRIMARY KEY,
  name        TEXT    NOT NULL,
  nickname    TEXT,
  email       TEXT    UNIQUE,
  points      INTEGER,
  referred_by INTEGER
);
INSERT INTO members VALUES
  (1, '佐藤', 'さとう', 'sato@example.com',   120, NULL),
  (2, '鈴木', '',       'suzuki@example.com',   0,    1),
  (3, '高橋', NULL,     NULL,                NULL,    1),
  (4, '田中', 'たなか', NULL,                  80,    2),
  (5, '伊藤', NULL,     'ito@example.com',   NULL, NULL);
""")

print("SQL を入力して Enter(終了は exit)")
while True:
    sql = input("sql> ").strip()
    if sql.lower() in ("exit", "quit"):
        break
    if not sql:
        continue
    try:
        cur = con.execute(sql)
    except sqlite3.Error as e:
        print("エラー:", e)
        continue
    if cur.description is None:
        print("(結果の表はありません)")
        continue
    print(" | ".join(d[0] for d in cur.description))
    rows = cur.fetchall()
    for row in rows:
        print(" | ".join("NULL" if v is None else str(v) for v in row))
    print(f"({len(rows)} 行)")

保存したフォルダーで次のコマンドを実行すると「sql>」と表示されるので、この記事の SQL を 1 行で入力して Enter を押します(Python 3.12・SQLite 3.45 で確認しました)。自分で書いた長い SQL は、SQL 整形ツールのモード「圧縮」で 1 行にできます。

py sql_null_practice.py

nickname(ニックネーム)・email・points(ポイント)・referred_by(紹介してくれた会員の id)に NULL が入っています。鈴木さんはニックネームが空文字でポイントが 0、高橋さんと伊藤さんはポイントが NULL(未集計)です。

id | name | nickname | email | points | referred_by
1 | 佐藤 | さとう | sato@example.com | 120 | NULL
2 | 鈴木 |  | suzuki@example.com | 0 | 1
3 | 高橋 | NULL | NULL | NULL | 1
4 | 田中 | たなか | NULL | 80 | 2
5 | 伊藤 | NULL | ito@example.com | NULL | NULL
(5 行)

「= NULL」ではなく IS NULL・IS NOT NULL で調べる

ポイントが未集計の会員を探すつもりで WHERE points = NULL と書くと、エラーにならずに 0 行が返ります。

SELECT name FROM members WHERE points = NULL;
name
(0 行)

NULL かどうかは IS NULL(NULL である)と IS NOT NULL(NULL ではない)で調べます。WHERE points IS NULL なら高橋・伊藤の 2 人、WHERE points IS NOT NULL なら佐藤・鈴木・田中の 3 人が返りました。

なぜ = ではだめなのかは、比較の結果を直接表示すると分かります。

SELECT NULL = NULL, NULL <> NULL, 1 = NULL, NULL IS NULL;
NULL = NULL | NULL <> NULL | 1 = NULL | NULL IS NULL
NULL | NULL | NULL | 1
(1 行)

NULL = NULL でさえ、真(1)ではなく NULL です。PostgreSQL の公式ドキュメントには「NULL は分からない値を表し、分からない値どうしが等しいかどうかは分からないので、式 = NULL と書いてはいけない」とあります。空文字と NULL の違いも、IS NULL で確かめられます。

SELECT name, nickname, nickname IS NULL AS 未設定, nickname = '' AS 空文字, length(nickname) AS 文字数 FROM members;
name | nickname | 未設定 | 空文字 | 文字数
佐藤 | さとう | 0 | 0 | 3
鈴木 |  | 0 | 1 | 0
高橋 | NULL | 1 | NULL | NULL
田中 | たなか | 0 | 0 | 3
伊藤 | NULL | 1 | NULL | NULL
(5 行)

鈴木さんの空文字は「NULL ではなく、長さ 0 の文字列」です。高橋さん・伊藤さんの NULL は、= '' の比較も length() の結果も NULL になりました。

3 値論理:TRUE・FALSE・UNKNOWN

SQL の条件式は、真(TRUE)と偽(FALSE)に加えて、不明(UNKNOWN)の 3 つの結果を持ちます。これを 3 値論理といいます。NULL との比較は UNKNOWN になり、SQLite や MySQL では UNKNOWN が NULL として表示されます。PostgreSQL の公式ドキュメントにも「SQL は true・false・null(unknown を表す)の 3 値論理を使う」とあります。

AND・OR・NOT の結果は次のとおりです(PostgreSQL の公式ドキュメントの真理値表と同じ内容で、SQLite でも SELECT NULL AND 0, NULL AND 1, NULL OR 0, NULL OR 1, NOT NULL; が 0・NULL・NULL・1・NULL になりました)。

ABA AND BA OR B
TRUEUNKNOWNUNKNOWNTRUE
FALSEUNKNOWNFALSEUNKNOWN
UNKNOWNUNKNOWNUNKNOWNUNKNOWN

NOT UNKNOWN は UNKNOWN のままです。「片方が FALSE なら AND は必ず FALSE」「片方が TRUE なら OR は必ず TRUE」のように、UNKNOWN の中身が真でも偽でも結果が同じになるときだけ、結果が決まると考えると覚えやすくなります。TRUE と FALSE だけの AND・OR・NOT の真理値表は、論理演算・真理値表ツールで論理式を入力すると作れます。

WHERE は TRUE の行だけを残す

WHERE は条件が TRUE の行だけを残し、FALSE と UNKNOWN の行は捨てます。そのため、「100 点未満」と「100 点以上」を OR でつないでも、全員にはなりません。

SELECT name, points FROM members WHERE points < 100 OR points >= 100;
name | points
佐藤 | 120
鈴木 | 0
田中 | 80
(3 行)

ポイントが NULL の高橋さん・伊藤さんは、どちらの比較も UNKNOWN なので消えます。同じように、WHERE points < 100(鈴木・田中)の結果を NOT で反転した WHERE NOT (points < 100) も、佐藤さんの 1 行だけでした。「条件に合う行」と「NOT を付けた条件に合う行」を足しても、全体にならないのが NULL のある列の特徴です。

points が 100 未満 OR 100 以上 の条件を 5 人の会員で評価した表。佐藤・鈴木・田中はどちらかが TRUE なので残る。points が NULL の高橋・伊藤はどちらの比較も UNKNOWN で、OR の結果も UNKNOWN なので WHERE で捨てられる
図 1:WHERE は TRUE の行だけを残す

NULL を含む計算と文字列の連結

NULL を含む計算は、ほぼすべて NULL になります。SQLite の公式ドキュメントにも「演算子は、どれかの値が NULL なら一般に NULL になる」とあり、例外は AND・OR・IS・IS NOT です。

SELECT name, points + 10 AS 加算後, points * 2 AS 二倍 FROM members;
name | 加算後 | 二倍
佐藤 | 130 | 240
鈴木 | 10 | 0
高橋 | NULL | NULL
田中 | 90 | 160
伊藤 | NULL | NULL
(5 行)

NULL * 0 も 0 ではなく NULL でした。文字列の連結も同じです。SQLite の ||(文字列をつなぐ演算子)で「名前(ニックネーム)」を作ってみます。

SELECT name, name || '(' || nickname || ')' AS 表示名 FROM members;
name | 表示名
佐藤 | 佐藤(さとう)
鈴木 | 鈴木()
高橋 | NULL
田中 | 田中(たなか)
伊藤 | NULL
(5 行)

ニックネームが NULL の 2 人は、名前ごと消えて NULL になりました。空文字の鈴木さんは「鈴木()」です。連結の関数は、データベースによって NULL の扱いが違うので注意が必要です。

書き方NULL があるとき確認方法
SQLite の ||結果が NULLSQLite 3.45 で実行
SQLite の concat()NULL を飛ばしてつなぐ(concat('a', NULL) は a)SQLite 3.45 で実行・公式ドキュメント
MySQL の CONCAT()結果が NULL公式マニュアル
MySQL の CONCAT_WS()区切り文字の後の NULL は飛ばす公式マニュアル
PostgreSQL の concat()・concat_ws()NULL を無視する公式ドキュメント

同じ「CONCAT」でも、MySQL では NULL になり、PostgreSQL と SQLite では NULL が無視されます。また MySQL の || は、既定では文字列の連結ではなく OR(論理和)の意味です(PIPES_AS_CONCAT という SQL モードを有効にしたときだけ連結になると公式マニュアルにあります)。どのデータベースでも同じ結果にしたいなら、次の COALESCE で NULL を先に置き換えてからつなぎます。

COALESCE・IFNULL・NULLIF で NULL を置き換える

COALESCE:最初の NULL でない値を返す

COALESCE(コアレス)は、引数を左から見て最初の NULL でない値を返す関数です。すべて NULL なら NULL を返します。SQL の標準にある関数で、SQLite・MySQL・PostgreSQL のどれでも使えます。

SELECT name, COALESCE(points, 0) AS points FROM members;
name | points
佐藤 | 120
鈴木 | 0
高橋 | 0
田中 | 80
伊藤 | 0
(5 行)

引数は 3 つ以上書けます(COALESCE(NULL, NULL, '3番目') は「3番目」)。「ニックネームがあればニックネーム、なければ名前」のように、優先順に候補を並べる使い方が便利です。

IFNULL:引数 2 つの COALESCE

IFNULL(email, '未登録') のように、1 つ目が NULL なら 2 つ目を返す関数です。SQLite の公式ドキュメントには「引数 2 つの coalesce() と同じ」とあります。高橋さん・田中さんの email が「未登録」になりました。IFNULL は SQLite と MySQL にはありますが、PostgreSQL の公式ドキュメントでは「ほかのデータベースの NVL や IFNULL と同様の機能が COALESCE」と紹介されているだけです。迷ったら COALESCE を使うと、データベースを変えても書き直さずに済みます(Oracle の NVL も同じ役割の関数です)。

NULLIF:特定の値を NULL に変える

NULLIF(A, B) は、A と B が等しければ NULL、違えば A を返します(NULLIF(5, 5) は NULL、NULLIF(5, 3) は 5)。COALESCE の逆向きの関数で、COALESCE と組み合わせると「空文字も NULL も未設定として扱う」ことができます。

SELECT name, COALESCE(NULLIF(nickname, ''), name) AS 表示名 FROM members;
name | 表示名
佐藤 | さとう
鈴木 | 鈴木
高橋 | 高橋
田中 | たなか
伊藤 | 伊藤
(5 行)

COALESCE(nickname, name) だけだと、空文字の鈴木さんの表示名は空のままでした。NULLIF で空文字を NULL に変えてから COALESCE に渡すのがポイントです。

COALESCE(NULLIF(nickname, ''), name) の流れ。佐藤さんの さとう はそのまま。鈴木さんの空文字は NULLIF で NULL になり、COALESCE で名前の 鈴木 に置き換わる。高橋さんの NULL も 高橋 に置き換わる
図 2:NULLIF と COALESCE の組み合わせ

NULLIF は「0 で割る」のを避けるときにも使われます。合計 / NULLIF(件数, 0) と書くと、件数が 0 のときは NULL で割ることになり、結果は NULL です。SQLite では 100 / 0 自体も NULL になり、MySQL の公式マニュアルにも IFNULL(1/0,10) が 10 になる例があります。ただ、0 での割り算をどう扱うかはデータベースや設定によって違うので、NULLIF で「0 なら割らない」ことをはっきり書いておくと、どこでも同じ結果になります。

集計関数と NULL:COUNT(*) と COUNT(列)、AVG

集計関数は、COUNT(*) を除いて NULL を無視します。詳しくは「SQLのGROUP BYと集計関数」で解説しているので、ここでは結果だけ確かめます。

SELECT COUNT(*), COUNT(points), SUM(points), AVG(points), AVG(COALESCE(points, 0)) FROM members;
COUNT(*) | COUNT(points) | SUM(points) | AVG(points) | AVG(COALESCE(points, 0))
5 | 3 | 200 | 66.66666666666667 | 40.0
(1 行)

COUNT(*) は行の数(5)、COUNT(points) は NULL でない値の数(3)です。AVG(points) は 200 ÷ 3、つまりポイントが分かっている 3 人の平均です。NULL を 0 点として 5 人で平均したいなら、COALESCE で 0 に置き換えます(200 ÷ 5 = 40)。どちらが正しいかは「未集計の人を平均に入れるべきか」で決まるので、集計の目的に合わせて選びます。

空文字を数えたくないときは、COUNT(NULLIF(nickname, '')) と書けます。COUNT(nickname) は空文字の鈴木さんも数えて 3、NULLIF を通すと 2 になりました。また、該当する行が 1 行もないと SUM は 0 ではなく NULL を返します(GROUP BY の記事の「合計が 0 ではなく NULL になる」を参照)。

ORDER BY での NULL の位置

ORDER BY points(昇順)を SQLite で実行すると、高橋さん・伊藤さん(NULL)が先頭に、DESC(降順)では最後に来ました。NULL の並ぶ位置は、データベースによって違います。

データベース昇順(ASC)降順(DESC)NULLS FIRST / LAST
SQLite先頭最後使える
MySQL先頭最後構文にない
PostgreSQL最後先頭使える

SQLite は実行結果、MySQL と PostgreSQL は公式マニュアル・ドキュメントで確認しました(PostgreSQL は「NULL は NULL でないどの値よりも大きいものとして並ぶ」と説明されています)。SQLite と PostgreSQL では ORDER BY points NULLS LAST で NULL を最後にできます。MySQL の SELECT 文の構文には NULLS LAST がないので、どのデータベースでも使える書き方として、IS NULL の結果(NULL なら 1、それ以外は 0)で先に並べる方法があります。

SELECT name, points FROM members ORDER BY points IS NULL, points;
name | points
鈴木 | 0
田中 | 80
佐藤 | 120
高橋 | NULL
伊藤 | NULL
(5 行)

NULL でない行は points IS NULL が 0、NULL の行は 1 になるので、まずそれで並べてから points で並べています。PostgreSQL なら NULLS LAST を使えば十分です。

NOT IN と NULL の落とし穴

「だれも紹介していない会員」を出すつもりで、紹介者の id の一覧に含まれない会員を NOT IN で探すと、0 行になります。

SELECT name FROM members WHERE id NOT IN (SELECT referred_by FROM members);
name
(0 行)

サブクエリの結果は (NULL, 1, 1, 2, NULL) で、NULL が含まれています。id NOT IN (…, NULL) は「id <> NULL」も AND で調べることになり、3 値論理の表のとおり結果は FALSE か UNKNOWN にしかならず、TRUE の行が 1 つも残りません。NOT EXISTS で書き直すと、高橋・田中・伊藤の 3 人が正しく返りました。

SELECT name FROM members WHERE NOT EXISTS (SELECT 1 FROM members AS r WHERE r.referred_by = members.id);

サブクエリに WHERE referred_by IS NOT NULL を付けて NULL を除いても同じ 3 人になります。仕組みと対策の詳細は「SQLのサブクエリとWITH句(CTE)」の「NOT IN と NULL の落とし穴」で解説しています。

NULL も「値」として比べる:IS DISTINCT FROM と SQLite の IS

「ポイントが 0 ではない会員」を <> で探すと、NULL の 2 人が抜けます。

SELECT name FROM members WHERE points <> 0;
name
佐藤
田中
(2 行)

「NULL も 0 とは違う値として数えたい」ときは、IS DISTINCT FROM を使います。NULL を普通の値のように比べる演算子で、WHERE points IS DISTINCT FROM 0 にすると佐藤・高橋・田中・伊藤の 4 人が返りました。

SELECT NULL IS DISTINCT FROM NULL, 1 IS DISTINCT FROM NULL, NULL IS NOT DISTINCT FROM NULL;
NULL IS DISTINCT FROM NULL | 1 IS DISTINCT FROM NULL | NULL IS NOT DISTINCT FROM NULL
0 | 1 | 1
(1 行)

結果が NULL にならず、必ず真か偽になるのが特徴です。データベースごとの書き方は次のとおりです。

データベースNULL も含めて「等しい」NULL も含めて「等しくない」
標準 SQL・PostgreSQLa IS NOT DISTINCT FROM ba IS DISTINCT FROM b
SQLitea IS b(上の書き方も可)a IS NOT b(上の書き方も可)
MySQLa <=> bNOT (a <=> b)

SQLite の公式ドキュメントでは、IS・IS NOT は「NULL が絡むとき以外は = と != と同じ」で、短い書き方は SQLite 独自の拡張だと説明されています。points IS NOT 0 も 4 人を返しました。MySQL の <=>(NULL 安全な等価演算子)は、公式マニュアルに「標準 SQL の IS NOT DISTINCT FROM と同じ」とあります。UPDATE の前に「値が変わる行だけ」を選ぶときなど、NULL から値へ・値から NULL への変化も見落としたくない比較に向いています。

UNIQUE 制約と NULL

email 列には UNIQUE 制約(同じ値を 2 回入れられない決まり)を付けています。ところが、email が NULL の会員はすでに 2 人いて、さらに 1 人追加してもエラーになりません。

INSERT INTO members (id, name, email) VALUES (6, '渡辺', NULL);

このあと SELECT id, name, email FROM members WHERE email IS NULL; を実行すると、高橋・田中・渡辺の 3 行が返りました。一方、既存の値と同じ sato@example.com を入れると「UNIQUE constraint failed: members.email」のエラーで止まります。UNIQUE 制約は、NULL どうしを「同じ値」とは見なさないのです。

データベースUNIQUE の列に NULL を複数入れられるか出典
SQLite入れられる公式ドキュメント・実行で確認
MySQL入れられる公式マニュアル(CREATE INDEX)
PostgreSQL既定では入れられる。15 以降は UNIQUE NULLS NOT DISTINCT で 1 つまでにできる公式ドキュメント・15.0 のリリースノート

PostgreSQL の公式ドキュメントには「SQL の標準では、UNIQUE 制約での NULL の扱いは実装によって決めてよいことになっており、ほかの実装では違う動きをする」とあります。「メールアドレスは必ず 1 人 1 つ」にしたいなら、UNIQUE に加えて NOT NULL 制約も付けましょう。

なお、SELECT DISTINCT email や GROUP BY email では、NULL は 1 つのグループにまとめられます(3 人の NULL が DISTINCT では 1 行、GROUP BY では「NULL | 3」になりました)。MySQL の公式マニュアルにも「GROUP BY では 2 つの NULL は等しいと見なされる」とあり、UNIQUE とは扱いが違う点に注意してください。

よくある間違い

「<> ”」で空でない行を探すと NULL も消える

WHERE nickname <> '' は佐藤・田中の 2 人で、NULL の行は入りません(目的によってはこれで正しい結果です)。逆に「ニックネームが未設定の会員」を探すときは、WHERE nickname = '' だけでは NULL の 2 人が漏れます。WHERE nickname IS NULL OR nickname = ''、または WHERE COALESCE(nickname, '') = '' で、鈴木・高橋・伊藤の 3 人が返りました。

CASE 列 WHEN NULL は一致しない

CASE points WHEN NULL THEN '未集計' ELSE '集計済み' END は、内部で points = NULL を調べるので、5 人全員が「集計済み」になりました。NULL を判定するときは CASE WHEN points IS NULL THEN '未集計' ELSE '集計済み' END と書きます。これで高橋・伊藤が「未集計」になります。

IN の一覧に NULL を入れても NULL の行は取れない

WHERE nickname IN ('さとう', NULL) は、佐藤さんの 1 行だけでした。IN は = の比較を OR でつないだものなので、NULL とは一致しません。WHERE nickname = 'さとう' OR nickname IS NULL と書き分けます。

よくある質問

未入力は NULL と空文字のどちらで保存すべきですか?

決まった正解はありませんが、1 つの列ではどちらかに統一することが大切です。両方が混ざると、この記事の鈴木さんと高橋さんのように、検索条件を 2 つ書く必要が出てきます。「分からない・まだない」を表したいなら NULL、値がないことが確定している文字列なら空文字、と意味で決めておき、必ず値が入る列には NOT NULL 制約を付けましょう。

CHECK 制約は NULL を防げますか?

防げません。PostgreSQL の公式ドキュメントには「CHECK 制約は、式が真または NULL になれば満たされる」とあり、CHECK (points >= 0) は points が NULL の行を通します。NULL を入れたくない列には NOT NULL 制約を付けます。

LEFT JOIN の結果に出てくる NULL も同じものですか?

扱いは同じ NULL です。LEFT JOIN で相手が見つからなかった行は右のテーブルの列が NULL になり、この記事と同じく IS NULL や COALESCE で扱います。詳しくは「SQLのJOINの種類と使い分け」を参照してください。

まとめ

  • NULL は「値がない・分からない」状態で、空文字や 0 とは別のもの。調べるときは = ではなく IS NULL・IS NOT NULL
  • NULL との比較は UNKNOWN になり、WHERE は TRUE の行だけを残す。NOT IN や <> で NULL の行が消える原因はこれ
  • NULL を含む計算や || の連結は NULL になる。COALESCE で置き換え、NULLIF で空文字や 0 を NULL に変える
  • COUNT(*) 以外の集計関数は NULL を無視する。並び順や CONCAT の動きはデータベースごとに違う
  • NULL どうしを等しいとみなす比較は IS NOT DISTINCT FROM(SQLite は IS、MySQL は <=>)。UNIQUE 制約は NULL を複数許す

関連する記事・ツール

ABOUT ME
hr
hr
エンジニアとして働いている hr です。仕事や資格勉強で「こんなツールがあれば」と感じたものを、無料Webツールと解説記事にして発信しています。
記事URLをコピーしました