SQLのNULLの扱い方|IS NULL・COALESCE・集計と比較の落とし穴
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 になりました)。
| A | B | A AND B | A OR B |
|---|---|---|---|
| TRUE | UNKNOWN | UNKNOWN | TRUE |
| FALSE | UNKNOWN | FALSE | UNKNOWN |
| UNKNOWN | UNKNOWN | UNKNOWN | UNKNOWN |
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 のある列の特徴です。

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 の || | 結果が NULL | SQLite 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 に渡すのがポイントです。

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・PostgreSQL | a IS NOT DISTINCT FROM b | a IS DISTINCT FROM b |
| SQLite | a IS b(上の書き方も可) | a IS NOT b(上の書き方も可) |
| MySQL | a <=> b | NOT (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 を複数許す
関連する記事・ツール
- SQL入門:SELECT文の基本|WHERE・ORDER BY・LIMITの使い方と注意点
- SQLのGROUP BYと集計関数|COUNT・SUM・AVG・HAVINGの使い方
- SQLのJOINの種類と使い分け|INNER・LEFT・RIGHT・FULLを実行結果で解説
- SQLのサブクエリとWITH句(CTE)|IN・EXISTS・再帰CTEを実行結果で解説
- 論理演算・真理値表ツール
- SQL整形ツール