SQLiteのNULL判定: IS NULL・COALESCE・IFNULL

NULLは「値が分からない」を表す

SQLiteで扱う値は、数値・文字列・BLOBなど、どれも何かしら具体的な中身を持っています。ところが NULL だけは別物。値が無い、あるいは分からないことを示すためのプレースホルダなんです。クエリで NULL がときどき不思議な振る舞いをする理由は、すべてこの一点に集約されます。

まずは動作確認用に、小さなテーブルを用意してみましょう。

CREATE TABLE users (
    id     INTEGER PRIMARY KEY,
    name   TEXT NOT NULL,
    email  TEXT,
    age    INTEGER
);

INSERT INTO users (name, email, age) VALUES
    ('Ada',   'ada@example.com', 30),
    ('Boris', NULL,              25),
    ('Cleo',  'cleo@example.com', NULL),
    ('Dan',   NULL,              NULL);

SELECT * FROM users;

2 つの列で NULL を許可しています。Boris はメールアドレスがなく、Cleo は年齢が未設定、Dan はどちらもありません。このページの残りでは、こうした行を SQLite で扱うときに引っかからないクエリの書き方を見ていきます。

SQLite で = や <> が NULL に効かない理由

最初に思いつくのは WHERE email = NULL という書き方でしょう。一見もっともらしく見えますが、実際には何も返ってきません。

CREATE TABLE users (
    id     INTEGER PRIMARY KEY,
    name   TEXT NOT NULL,
    email  TEXT,
    age    INTEGER
);

INSERT INTO users (name, email, age) VALUES
    ('Ada',   'ada@example.com', 30),
    ('Boris', NULL,              25),
    ('Cleo',  'cleo@example.com', NULL),
    ('Dan',   NULL,              NULL);

SELECT name, email FROM users WHERE email = NULL;

結果はゼロ件です。Boris と Dan のメールが明らかに NULL なのに、なぜでしょうか。理由はシンプルで、NULL との比較は true でも false でもなく、結果が NULL になるからです。SQLite の WHERE 句は条件が true の行だけを残す仕様なので、NULL は true ではない以上、その行は弾かれてしまいます。

<> でも同じ落とし穴にハマります。

CREATE TABLE users (
    id     INTEGER PRIMARY KEY,
    name   TEXT NOT NULL,
    email  TEXT,
    age    INTEGER
);

INSERT INTO users (name, email, age) VALUES
    ('Ada',   'ada@example.com', 30),
    ('Boris', NULL,              25),
    ('Cleo',  'cleo@example.com', NULL),
    ('Dan',   NULL,              NULL);

SELECT name, email FROM users WHERE email <> 'ada@example.com';

これだとAda以外の全員が返ってきそうですが、実際にはCleoしかヒットしません。emailがNULLになっているBorisとDanは結果から落ちてしまいます。なぜならNULL <> 'ada@example.com'の結果もまたNULLであって、trueではないからです。

これはSQLで一番よくある落とし穴です。「あれ、想定より行が減ってる…」というクエリを見たら、まずNULLを含むカラムを疑ってください。

IS NULLとIS NOT NULLを使う

SQLiteでNULL判定をする正しい方法はIS演算子です。=とは違って、ISはNULLをきちんと理解していて、結果は必ずtrueかfalseのどちらか。NULLを返すことはありません。

CREATE TABLE users (
    id     INTEGER PRIMARY KEY,
    name   TEXT NOT NULL,
    email  TEXT,
    age    INTEGER
);

INSERT INTO users (name, email, age) VALUES
    ('Ada',   'ada@example.com', 30),
    ('Boris', NULL,              25),
    ('Cleo',  'cleo@example.com', NULL),
    ('Dan',   NULL,              NULL);

SELECT name FROM users WHERE email IS NULL;

SELECT name FROM users WHERE email IS NOT NULL;

1つ目のクエリは Boris と Dan、2つ目は Ada と Cleo を返します。「この値は欠けている？」と尋ねるために用意されているのが IS NULL と IS NOT NULL の2つの演算子です。= NULL や <> NULL と書きたくなる場面では、必ずこちらを使ってください。

「Ada 以外（NULL も含めて）」を取りたいときは、次のように条件を明示的に組み合わせます。

CREATE TABLE users (
    id     INTEGER PRIMARY KEY,
    name   TEXT NOT NULL,
    email  TEXT,
    age    INTEGER
);

INSERT INTO users (name, email, age) VALUES
    ('Ada',   'ada@example.com', 30),
    ('Boris', NULL,              25),
    ('Cleo',  'cleo@example.com', NULL),
    ('Dan',   NULL,              NULL);

SELECT name, email
FROM users
WHERE email <> 'ada@example.com' OR email IS NULL;

これでボリス、クレオ、ダンの3人が表示されます。

NULL は算術演算や文字列連結にも伝播する

「不明」ルールは比較演算だけにとどまりません。NULL を含む演算は、何をやっても結果が NULL になります。

CREATE TABLE users (
    id     INTEGER PRIMARY KEY,
    name   TEXT NOT NULL,
    email  TEXT,
    age    INTEGER
);

INSERT INTO users (name, email, age) VALUES
    ('Ada',   'ada@example.com', 30),
    ('Boris', NULL,              25),
    ('Cleo',  'cleo@example.com', NULL),
    ('Dan',   NULL,              NULL);

SELECT
    name,
    age,
    age + 1       AS next_year,
    age * 2       AS doubled,
    'こんにちは ' || name AS greeting,
    '年齢: ' || age AS labelled_age
FROM users;

next_year と doubled は Cleo と Dan のところが null になります。labelled_age も同じく null です。文字列と NULL を連結すると 'Age: ' ではなく NULL が返ってくるからですね。null になり得るカラムから使える値を取り出したいなら、こちら側でちゃんと処理してあげる必要があります。そこで登場するのが、これから紹介する 2 つの関数です。

IFNULL: 2 引数で使うフォールバック

IFNULL(a, b) は a を返しますが、a が null のときは代わりに b を返します。null をデフォルト値に置き換えたいときに、いちばんシンプルに書ける方法です。

CREATE TABLE users (
    id     INTEGER PRIMARY KEY,
    name   TEXT NOT NULL,
    email  TEXT,
    age    INTEGER
);

INSERT INTO users (name, email, age) VALUES
    ('Ada',   'ada@example.com', 30),
    ('Boris', NULL,              25),
    ('Cleo',  'cleo@example.com', NULL),
    ('Dan',   NULL,              NULL);

SELECT
    name,
    IFNULL(email, '(メールなし)') AS email,
    IFNULL(age, 0)              AS age
FROM users;

BorisさんとDanさんは (メールなし) に、CleoさんとDanさんは 0 になります。元のデータ自体が変わるわけではなく、IFNULL はあくまで出力結果を置き換えているだけです。

IFNULL は必ず引数を2つだけ取る関数です。もっと多くのフォールバック値を指定したいときは、COALESCE の出番です。

COALESCE：最初に見つかった非NULL値を返す

COALESCE(a, b, c, ...) は引数を順番にチェックしていき、最初にNULLでなかった値を返します。IFNULL を任意の数のフォールバックに拡張したもの、と考えるとわかりやすいでしょう。

CREATE TABLE users (
    id     INTEGER PRIMARY KEY,
    name   TEXT NOT NULL,
    email  TEXT,
    age    INTEGER
);

INSERT INTO users (name, email, age) VALUES
    ('Ada',   'ada@example.com', 30),
    ('Boris', NULL,              25),
    ('Cleo',  'cleo@example.com', NULL),
    ('Dan',   NULL,              NULL);

SELECT
    name,
    COALESCE(email, name || '@unknown.local', '匿名') AS contact
FROM users;

Ada と Cleo については email がそのまま使われます。Boris と Dan は email が NULL なので、SQLite は第2引数（名前から組み立てたアドレス）を評価します。仮にそれも NULL だったら、最後の '匿名' まで落ちる仕組みです。

COALESCE は移植性が高く、主要な SQL データベースならどれも同じ挙動でサポートしています。一方 IFNULL は SQLite と MySQL で使える、引数2つ専用のショートカットです。基本は COALESCE を選び、引数がちょうど2つで短く書きたいときだけ IFNULL を使う、というスタンスがおすすめです。

NULL と空文字列は別物

よくある勘違いとして、NULL と ''（空文字）を同じものとして扱ってしまうケースがあります。実はこの2つ、まったくの別物です。

CREATE TABLE users (
    id     INTEGER PRIMARY KEY,
    name   TEXT NOT NULL,
    email  TEXT,
    age    INTEGER
);

INSERT INTO users (name, email, age) VALUES
    ('Ada',   'ada@example.com', 30),
    ('Boris', NULL,              25),
    ('Cleo',  'cleo@example.com', NULL),
    ('Dan',   NULL,              NULL);

SELECT
    '' IS NULL          AS empty_is_null,
    NULL IS NULL        AS null_is_null,
    length('')          AS empty_length,
    length(NULL)        AS null_length,
    '' = ''             AS empty_equals_empty,
    NULL = NULL         AS null_equals_null;

'' は文字数がゼロの「ちゃんとした文字列」で、NULL は値そのものが存在しない状態です。length('') は 0 を返しますが、length(NULL) の結果は NULL のまま。そして NULL = NULL は 1 ではなく NULL になります。だからこそ IS NULL という構文がわざわざ用意されているわけです。

ひとつのカラムに '' と NULL の両方が混ざり得る場合は、「値がない」をどちらで表現するか決めて、必ずそれで統一しましょう。両方使ってしまうと、クエリを書くたびに 2 パターンを意識しないといけなくなり、いつか必ず どちらかを書き忘れます。

IN・NOT IN・DISTINCT での NULL の扱い

NULL がこっそり顔を出す場面は、ほかにもいくつかあります。

リストに NULL を含めて IN を使うと、想定外の結果になることがあります。とくに NOT IN では要注意です。

CREATE TABLE users (
    id     INTEGER PRIMARY KEY,
    name   TEXT NOT NULL,
    email  TEXT,
    age    INTEGER
);

INSERT INTO users (name, email, age) VALUES
    ('Ada',   'ada@example.com', 30),
    ('Boris', NULL,              25),
    ('Cleo',  'cleo@example.com', NULL),
    ('Dan',   NULL,              NULL);

SELECT name, age FROM users WHERE age NOT IN (25, NULL);

25歳以外の全員が返ってくると思いますよね。ところが結果はゼロ件です。SQLite は NOT IN (25, NULL) をだいたい age <> 25 AND age <> NULL に展開するのですが、age <> NULL の結果は常に NULL になるため、条件全体が決して真になりません。対処法としては、比較する前にリスト側（または列側）から NULL を除外しておくことです。

一方で DISTINCT は、重複排除の場面では NULL 同士を等しいものとして扱います。

CREATE TABLE users (
    id     INTEGER PRIMARY KEY,
    name   TEXT NOT NULL,
    email  TEXT,
    age    INTEGER
);

INSERT INTO users (name, email, age) VALUES
    ('Ada',   'ada@example.com', 30),
    ('Boris', NULL,              25),
    ('Cleo',  'cleo@example.com', NULL),
    ('Dan',   NULL,              NULL);

SELECT DISTINCT email FROM users;

結果は3行返ってきます。Adaのメール、Cleoのメール、そしてBorisとDanがまとめられた1つのNULLです。GROUP BYやUNIONも同じで、NULL同士を1つのグループとして扱います。これは=の挙動とは真逆ですね。SQLはこのあたり一貫性がないので、どの演算子がどちら側のルールに従うのかを把握しておくと役立ちます。

SQLite NULLの取り扱い チェックリスト

• 欠損値の判定にはIS NULL / IS NOT NULLを使う。= NULLは絶対NG。
• NULLが絡む算術演算・文字列連結・比較は、すべて結果がNULLになる。
• NULLを別の値に置き換えたいときはCOALESCE(a, b, c, ...)。引数2つで済むならIFNULL(a, b)が手軽。
• 空文字''とNULLは別物。各カラムで「欠損」をどちらで表現するか、最初に決めておく。
• NOT IN (..., NULL)はほぼ確実にバグの原因。リストからNULLを除いてから使うこと。

次は並べ替え

NULLも含めて行を正しく絞り込めるようになったら、次は使いやすい順番に並べる番です。次ページで扱うORDER BYにも、ソート結果のどこにNULLを置くかという独自のルールがあります。