2026.10.04
- MySQL
MySQL IF・IFNULLの使い方｜CASE・COALESCEとの違い
MySQLのIFは「条件が真ならA、そうでなければB」を返す関数、IFNULLは「値がNULLなら代わりの値」を返す関数です。 よく似た NULLIF・COALESCE・CASE と合わせて5つを押さえると、SELECT の中の条件分岐はほぼすべて書けるようになります。
この記事では、5つの違いを早見表にまとめ、同じテーブルに対して5つを実行した結果を並べました。条件付き集計、ゼロ除算の回避、空文字と NULL の扱い、並べ替え、UPDATE でのコピペ用 SQL も、すべて MySQL 8.0.46 で実際に実行した出力つきで載せています。
IF・IFNULL・NULLIF・COALESCE・CASEの早見表
| 関数・式 | 引数の数 | 返す値 | 標準SQL | 主な用途 |
|---|---|---|---|---|
IF(条件, A, B) |
3 | 条件が真なら A、偽または NULL なら B | MySQL 独自 | 2択の分岐、条件付き集計 |
IFNULL(X, Y) |
2 | X が NULL でなければ X、NULL なら Y | MySQL 独自 | NULL を既定値に置き換える |
NULLIF(X, Y) |
2 | X = Y なら NULL、違えば X | 標準 | 0 や空文字を NULL に変える、ゼロ除算の回避 |
COALESCE(X, Y, ...) |
1以上 | 左から見て最初の NULL でない値。すべて NULL なら NULL | 標準 | 複数の候補から最初の値を選ぶ |
CASE WHEN 条件 THEN A ... ELSE Z END |
条件をいくつでも | 最初に真になった WHEN の値。どれも真でなく ELSE も無ければ NULL | 標準 | 3択以上の分岐、範囲での区分け |
「標準SQL」の列は一般に知られた仕様に基づく記載です。後半で PostgreSQL 16 でも CASE・NULLIF・COALESCE が動き、IF・IFNULL は関数が無いことを実行して確かめています。
迷ったときの選び方
| やりたいこと | 選ぶもの | 例 |
|---|---|---|
| NULL を 0 や文字に置き換えたい | IFNULL（候補が3つ以上なら COALESCE） | IFNULL(points, 0) |
| 2択で分けたい | IF | IF(points >= 1000, '上位', '通常') |
| 3択以上で分けたい | CASE | CASE WHEN ... THEN ... END |
| 0 で割るのを避けたい | NULLIF | a / NULLIF(b, 0) |
| 空文字も NULL と同じに扱いたい | NULLIF と IFNULL の組み合わせ | IFNULL(NULLIF(col, ''), '(未設定)') |
| 他のデータベースでも同じ SQL を使いたい | CASE / COALESCE / NULLIF | IF と IFNULL は書き換えが必要 |
同じテーブルで5つを実行して比べる
検証用のテーブルです。NULL、0、空文字、空白だけの文字列をわざと混ぜています。
CREATE TABLE members (
id INT PRIMARY KEY, name VARCHAR(20), nickname VARCHAR(20),
points INT, orders INT, rank_code CHAR(1), memo VARCHAR(20)
);
INSERT INTO members VALUES
(1,'佐藤','さとちゃん',1200,4,'A','VIP'),
(2,'鈴木',NULL,300,0,'B',''),
(3,'高橋','',NULL,2,'C',NULL),
(4,'田中','たなか',0,0,NULL,' '),
(5,'伊藤',NULL,NULL,NULL,'A','新規');
SELECT id, name,
IF(points >= 1000, '上位', '通常') AS if_r,
IFNULL(points, 0) AS ifnull_r,
NULLIF(points, 0) AS nullif_r,
COALESCE(nickname, memo, name) AS coalesce_r,
CASE WHEN points >= 1000 THEN '上位'
WHEN points >= 1 THEN '通常'
WHEN points = 0 THEN 'ゼロ'
ELSE '未設定' END AS case_r
FROM members ORDER BY id;
実行結果（MySQL 8.0.46）:
+----+--------+--------+----------+----------+-----------------+-----------+
| id | name | if_r | ifnull_r | nullif_r | coalesce_r | case_r |
+----+--------+--------+----------+----------+-----------------+-----------+
| 1 | 佐藤 | 上位 | 1200 | 1200 | さとちゃん | 上位 |
| 2 | 鈴木 | 通常 | 300 | 300 | | 通常 |
| 3 | 高橋 | 通常 | 0 | NULL | | 未設定 |
| 4 | 田中 | 通常 | 0 | NULL | たなか | ゼロ |
| 5 | 伊藤 | 通常 | 0 | NULL | 新規 | 未設定 |
+----+--------+--------+----------+----------+-----------------+-----------+
この1つの結果に、5つの性質がすべて表れています。
- IF は、条件が NULL のときも「偽」の側を返します。 points が NULL の id=3、5 は
NULL >= 1000が NULL になり、「通常」になりました。未設定を区別したいなら CASE か、IF を入れ子にします。 - IFNULL は NULL だけを置き換えます。 id=3、5 が 0 になりました。もともと 0 の id=4 と見分けが付かなくなる点は意識しておきます。
- NULLIF は逆向きで、指定した値（ここでは 0）を NULL に変えます。 id=4 の 0 が NULL になりました。
- COALESCE は左から順に、最初の NULL でない値を返します。 id=5 は nickname が NULL なので memo の「新規」になりました。id=2 は memo が空文字、id=3 は nickname が空文字で、空文字は NULL ではないため、そのまま選ばれています。（対処は後の「空文字と NULL をまとめて扱う」で説明します。）
- CASE は上から順に評価し、最初に真になったところで決まります。 NULL はどの比較も真にならないので ELSE の「未設定」になりました。
IFの使い方（2択の分岐）
条件の真偽の決まり方
SELECT IF(NULL,'真','偽') AS a,
IF(0,'真','偽') AS b,
IF('abc','真','偽') AS c,
IF(2,'真','偽') AS d,
IF(NULL = NULL,'等しい','等しくない/不明') AS e;
+-----+-----+-----+-----+------------------------+
| a | b | c | d | e |
+-----+-----+-----+-----+------------------------+
| 偽 | 偽 | 偽 | 真 | 等しくない/不明 |
+-----+-----+-----+-----+------------------------+
| 条件の値 | IF の結果 | 補足 |
|---|---|---|
| 0 以外の数値 | 真の側 | 2 でも −1 でも真 |
| 0 | 偽の側 | |
| NULL | 偽の側 | NULL = NULL も NULL になるので偽の側 |
| 数字で始まらない文字列（'abc'） | 偽の側 | 数値 0 として評価され、警告 1292（Truncated incorrect DOUBLE value: 'abc'）が出る |
NULL かどうかの判定には = NULL ではなく IS NULL を使います。 points = NULL は常に NULL になり、points <=> NULL（NULL 安全な等価演算子）は 1 か 0 を返すことも確認しました。
NULLを区別したいときは入れ子にするかCASEにする
SELECT id, points,
IF(points > 500, '多い', '少ない') AS simple,
IF(points IS NULL, '未設定', IF(points > 500, '多い', '少ない')) AS nested
FROM members;
+----+--------+-----------+-----------+
| id | points | simple | nested |
+----+--------+-----------+-----------+
| 1 | 1200 | 多い | 多い |
| 2 | 300 | 少ない | 少ない |
| 3 | NULL | 少ない | 未設定 |
| 4 | 0 | 少ない | 少ない |
| 5 | NULL | 少ない | 未設定 |
+----+--------+-----------+-----------+
入れ子が2段以上になったら、CASE に書き換えると読みやすくなります。
IF関数とIF文は別のもの
SELECT の中で使う IF(条件, A, B) は関数です。ストアドプログラムの中で使う IF ... THEN ... END IF は制御文で、書き方が違います。
DELIMITER //
CREATE FUNCTION grade(p INT) RETURNS VARCHAR(10) DETERMINISTIC
BEGIN
IF p IS NULL THEN RETURN '未設定';
ELSEIF p >= 1000 THEN RETURN '上位';
ELSE RETURN '通常';
END IF;
END//
DELIMITER ;
SELECT id, grade(points) FROM members;
-- 1:上位 2:通常 3:未設定 4:通常 5:未設定（実行して確認）
IFNULLとCOALESCEの使い方（NULLを置き換える）
SELECT COALESCE(NULL, NULL) AS a,
COALESCE(NULL, 0, 5) AS b,
IFNULL(NULL, NULL) AS c;
-- a: NULL / b: 0 / c: NULL
- IFNULL は引数が必ず2つです。3つ渡すと
ERROR 1582 (42000): Incorrect parameter count in the call to native function 'IFNULL'になりました。 - COALESCE は引数をいくつでも並べられます。
COALESCE(NULL, 0, 5)が 0 を返すとおり、0 は NULL ではないので選ばれます。 - 2つの引数で使うなら、IFNULL(X, Y) と COALESCE(X, Y) は同じ結果になります。
| 比較 | IFNULL | COALESCE |
|---|---|---|
| 引数 | 2つ固定 | 1つ以上 |
| 標準SQL | MySQL 独自 | 標準 |
| 向いている場面 | MySQL だけで使う短い SQL | 候補が3つ以上、他の製品でも使う SQL |
集計結果が NULL になるのを 0 にする
対象の行が0件のとき、SUM は 0 ではなく NULL を返します。 画面や帳票に出す値は IFNULL で包んでおくと安心です。
SELECT (SELECT SUM(points) FROM members WHERE id = 99) AS s_none,
IFNULL((SELECT SUM(points) FROM members WHERE id = 99), 0) AS s_none0;
-- s_none: NULL / s_none0: 0
平均を取るときは「NULLを0にするか」で結果が変わる
SELECT IFNULL(rank_code,'(なし)') AS rank_code, COUNT(*) AS n,
SUM(IFNULL(points,0)) AS pts,
AVG(points) AS avg_skip_null,
AVG(IFNULL(points,0)) AS avg_zero
FROM members GROUP BY rank_code ORDER BY 1;
+-----------+---+------+---------------+----------+
| rank_code | n | pts | avg_skip_null | avg_zero |
+-----------+---+------+---------------+----------+
| (なし) | 1 | 0 | 0.0000 | 0.0000 |
| A | 2 | 1200 | 1200.0000 | 600.0000 |
| B | 1 | 300 | 300.0000 | 300.0000 |
| C | 1 | 0 | NULL | 0.0000 |
+-----------+---+------+---------------+----------+
ランク A は「1200 と NULL」の2人です。AVG(points) は NULL を除いて 1200、AVG(IFNULL(points,0)) は NULL を 0 として 600 になります。「未入力は対象外」なのか「未入力は 0 点」なのかを決めてから選びます。
NULLIFの使い方（ゼロ除算の回避）
SELECT NULLIF(NULL, 1) AS d, -- NULL
NULLIF(1, NULL) AS e, -- 1
NULLIF('a', 'A') AS f, -- NULL（既定の照合順序では大文字小文字を区別しない）
NULLIF('a ', 'a') AS g; -- 'a '（今回の環境では末尾の空白は区別された）
NULLIF の比較は = と同じ規則です。文字列では、列や接続の照合順序によって大文字小文字の扱いが変わります。
0で割る計算を安全にする
SELECT id, points, orders,
points / orders AS raw_div,
points / NULLIF(orders, 0) AS safe_div,
IFNULL(ROUND(points / NULLIF(orders, 0), 1), 0) AS with_default
FROM members;
+----+--------+--------+----------+----------+--------------+
| id | points | orders | raw_div | safe_div | with_default |
+----+--------+--------+----------+----------+--------------+
| 1 | 1200 | 4 | 300.0000 | 300.0000 | 300.0 |
| 2 | 300 | 0 | NULL | NULL | 0.0 |
| 3 | NULL | 2 | NULL | NULL | 0.0 |
| 4 | 0 | 0 | NULL | NULL | 0.0 |
| 5 | NULL | NULL | NULL | NULL | 0.0 |
+----+--------+--------+----------+----------+--------------+
SELECT では、どちらの書き方も結果は NULL です。違いは警告とエラーに出ます。points / orders のほうは 0 で割った2行について警告が出ました。
+---------+------+---------------+
| Level | Code | Message |
+---------+------+---------------+
| Warning | 1365 | Division by 0 |
| Warning | 1365 | Division by 0 |
+---------+------+---------------+
さらに、既定の sql_mode（STRICT_TRANS_TABLES と ERROR_FOR_DIVISION_BY_ZERO を含む）では、INSERT や UPDATE の中で 0 で割るとエラーで止まります。
CREATE TABLE d (x INT);
INSERT INTO d SELECT 10 / 0;
-- ERROR 1365 (22012): Division by 0
INSERT INTO d SELECT 10 / NULLIF(0, 0);
-- 成功。x に NULL が入る
割る数を NULLIF(列, 0) で包んでおくと、SELECT では警告が出ず、INSERT・UPDATE でも止まりません。 結果を 0 と表示したい場合は、外側を IFNULL で包みます（上の with_default 列）。
CASEの使い方（3択以上の分岐）
CASE には2つの書き方があります。
| 書き方 | 形 | 向いている場面 |
|---|---|---|
| 単純 CASE | CASE 列 WHEN 値1 THEN ... WHEN 値2 THEN ... END |
1つの列を、決まった値と比べる |
| 検索 CASE | CASE WHEN 条件1 THEN ... WHEN 条件2 THEN ... END |
範囲、複数の列、IS NULL などの条件 |
SELECT id, rank_code,
CASE rank_code WHEN 'A' THEN 'ゴールド' WHEN 'B' THEN 'シルバー' END AS no_else,
CASE rank_code WHEN 'A' THEN 'ゴールド' WHEN NULL THEN 'なし' ELSE 'その他' END AS when_null,
CASE WHEN rank_code IS NULL THEN 'なし' ELSE rank_code END AS searched
FROM members;
+----+-----------+--------------+--------------+----------+
| id | rank_code | no_else | when_null | searched |
+----+-----------+--------------+--------------+----------+
| 1 | A | ゴールド | ゴールド | A |
| 2 | B | シルバー | その他 | B |
| 3 | C | NULL | その他 | C |
| 4 | NULL | NULL | その他 | なし |
| 5 | A | ゴールド | ゴールド | A |
+----+-----------+--------------+--------------+----------+
- ELSE を書かないと、どの WHEN にも当てはまらない行は NULL になります（no_else の id=3、4）。
- 単純 CASE の
WHEN NULLは一致しません（when_null の id=4 が「なし」ではなく「その他」）。内部ではrank_code = NULLと比べているためです。NULL を判定するときは、検索 CASE でWHEN rank_code IS NULLと書きます。
条件付き集計のコピペ集（SUM(IF)とCOUNT(CASE)）
WHERE で絞らずに、1回の SELECT で複数の条件の件数や合計を出す書き方です。
SELECT COUNT(*) AS total,
SUM(IF(rank_code = 'A', 1, 0)) AS sum_if,
COUNT(IF(rank_code = 'A', 1, NULL)) AS count_if,
COUNT(CASE WHEN rank_code = 'A' THEN 1 END) AS count_case,
SUM(rank_code = 'A') AS sum_bool,
SUM(CASE WHEN points >= 1000 THEN points ELSE 0 END) AS pts_hi,
COUNT(IF(rank_code = 'A', 1, 0)) AS count_if_zero
FROM members;
+-------+--------+----------+------------+----------+--------+---------------+
| total | sum_if | count_if | count_case | sum_bool | pts_hi | count_if_zero |
+-------+--------+----------+------------+----------+--------+---------------+
| 5 | 2 | 2 | 2 | 2 | 1200 | 5 |
+-------+--------+----------+------------+----------+--------+---------------+
| 書き方 | 結果 | ポイント |
|---|---|---|
SUM(IF(条件, 1, 0)) |
2 | 条件に合う行を 1 として合計する |
COUNT(IF(条件, 1, NULL)) |
2 | COUNT は NULL を数えないので、合わない行を NULL にする |
COUNT(CASE WHEN 条件 THEN 1 END) |
2 | ELSE を省くと NULL になる性質を使う。他の製品でも使える書き方 |
SUM(条件) |
2 | MySQL では比較の結果が 1 / 0 になる。短いが MySQL 向けの書き方 |
COUNT(IF(条件, 1, 0)) |
5 | 0 は NULL ではないので全行が数えられる。意図と違う結果になる例 |
COUNT と組み合わせるときは「合わない行を NULL にする」、SUM と組み合わせるときは「合わない行を 0 にする」 と覚えると迷いません。
条件ごとの最大・最小・割合
SELECT MAX(CASE WHEN rank_code = 'A' THEN points END) AS max_a, -- 1200
MIN(IF(rank_code = 'B', points, NULL)) AS min_b, -- 300
COUNT(DISTINCT IF(points > 0, rank_code, NULL)) AS kinds, -- 2
ROUND(100 * SUM(points IS NULL) / COUNT(*), 1) AS null_pct -- 40.0
FROM members;
グループごとに出したいときは、同じ式に GROUP BY を付けます。
SELECT rank_code, COUNT(*) AS n, SUM(orders > 0) AS has_order
FROM members GROUP BY rank_code;
-- A: n=2, has_order=1 / B: 1, 0 / C: 1, 1 / NULL: 1, 0（実行して確認）
空文字とNULLをまとめて扱う
「未入力」が NULL と空文字の両方で入っているテーブルでは、NULLIF で空文字を NULL にそろえてから IFNULL で置き換える書き方が便利です。
SELECT id, nickname,
IFNULL(nickname, '(未設定)') AS only_null,
IFNULL(NULLIF(nickname, ''), '(未設定)') AS null_and_empty,
COALESCE(NULLIF(TRIM(memo), ''), '(メモなし)') AS memo_trim,
IF(nickname IS NULL OR nickname = '', '(未設定)', nickname) AS by_if
FROM members;
+----+-----------------+-----------------+-----------------+----------------+-----------------+
| id | nickname | only_null | null_and_empty | memo_trim | by_if |
+----+-----------------+-----------------+-----------------+----------------+-----------------+
| 1 | さとちゃん | さとちゃん | さとちゃん | VIP | さとちゃん |
| 2 | NULL | (未設定) | (未設定) | (メモなし) | (未設定) |
| 3 | | | (未設定) | (メモなし) | (未設定) |
| 4 | たなか | たなか | たなか | (メモなし) | たなか |
| 5 | NULL | (未設定) | (未設定) | 新規 | (未設定) |
+----+-----------------+-----------------+-----------------+----------------+-----------------+
- only_null（IFNULL だけ）では、id=3 の空文字がそのまま残ります。
IFNULL(NULLIF(列, ''), 既定値)で、NULL と空文字の両方を置き換えられます。- 空白だけの文字列（id=4 の memo）も対象にするなら、TRIM を挟みます。
WHERE 句で「未入力の行」を探すときは、次の3つが同じ件数（3件）を返しました。
SELECT COUNT(*) FROM members WHERE nickname IS NULL OR nickname = '';
SELECT COUNT(*) FROM members WHERE NULLIF(nickname, '') IS NULL;
SELECT COUNT(*) FROM members WHERE IFNULL(nickname, '') = '';
ORDER BYで並べ替えに使う
NULLを最後に回す
MySQL の昇順では NULL が先頭に来ます。
SELECT id, points FROM members ORDER BY points;
-- 並び: 3(NULL), 5(NULL), 4(0), 2(300), 1(1200)
SELECT id, points FROM members ORDER BY points IS NULL, points;
-- 並び: 4(0), 2(300), 1(1200), 3(NULL), 5(NULL)
points IS NULL は NULL の行で 1、それ以外で 0 になるので、先にこの式で並べると NULL が後ろに回ります。降順（ORDER BY points DESC）では、もともと NULL が最後になりました。
決まった順番で並べる
SELECT id, rank_code FROM members
ORDER BY CASE rank_code WHEN 'B' THEN 1 WHEN 'A' THEN 2 ELSE 9 END, id;
+----+-----------+
| id | rank_code |
+----+-----------+
| 2 | B |
| 1 | A |
| 5 | A |
| 3 | C |
| 4 | NULL |
+----+-----------+
MySQL には FIELD(rank_code, 'B', 'A') という関数もありますが、リストに無い値と NULL は 0 を返すため先頭に並びます（今回の結果は C、NULL、B、A、A の順）。リストに無い値を後ろに回したいときは、ELSE を書ける CASE のほうが扱いやすいです。
特定の行を先頭に固定する
SELECT id, name FROM members ORDER BY IF(id = 3, 0, 1), id;
-- 並び: 3, 1, 2, 4, 5
UPDATEで条件付き更新をする
行ごとに違う計算をする
UPDATE members SET points = IF(points IS NULL, 0, points + 100);
-- 5行が更新され、1200→1300、300→400、NULL→0、0→100、NULL→0
複数の列を一度に区分けする
UPDATE members
SET rank_code = CASE WHEN points >= 1000 THEN 'A'
WHEN points >= 300 THEN 'B'
ELSE 'C' END,
memo = IFNULL(NULLIF(TRIM(memo), ''), '(なし)');
+----+--------+-----------+----------+
| id | points | rank_code | memo |
+----+--------+-----------+----------+
| 1 | 1300 | A | VIP |
| 2 | 400 | B | (なし) |
| 3 | 0 | C | (なし) |
| 4 | 100 | C | (なし) |
| 5 | 0 | C | 新規 |
+----+--------+-----------+----------+
（上の2つは、members をコピーしたテーブルに対して続けて実行した結果です。）
idごとに違う値を入れるときはELSEかWHEREを付ける
UPDATE members SET points = CASE id WHEN 1 THEN 10 WHEN 2 THEN 20 END;
+----+--------+
| id | points |
+----+--------+
| 1 | 10 |
| 2 | 20 |
| 3 | NULL |
| 4 | NULL |
| 5 | NULL |
+----+--------+
ELSE の無い CASE は、当てはまらない行に NULL を返すので、id=3〜5 の points が NULL で上書きされました。 対象外の行を変えないためには、次のどちらか（両方でも可）を付けます。
UPDATE members
SET points = CASE id WHEN 1 THEN 11 WHEN 2 THEN 22 ELSE points END
WHERE id IN (1, 2);
戻り値の型が変わる例
IF や IFNULL の2つの値に、数値と文字列のように違う型を混ぜると、結果の型は文字列側にそろいます。 CREATE TABLE ... AS SELECT で作った列の型を見ると分かります。
CREATE TABLE ty AS SELECT
IF(1, 1, 'a') AS if_int_str,
IF(1, 1, 2.5) AS if_int_dec,
IF(1, 1, 2e0) AS if_int_dbl,
IFNULL(1, 'x') AS ifnull_int_str,
IFNULL(NULL, 1) AS ifnull_null_int,
NULLIF(1, 2) AS nullif_int,
COALESCE(NULL, 1, 'x') AS coal_mix,
CASE WHEN 1 THEN 1 ELSE 'x' END AS case_mix,
IF(1, NOW(), 'x') AS if_dt_str,
IF(1, CURDATE(), NOW()) AS if_date_dt,
IFNULL(points, 'なし') AS ifnull_col
FROM members LIMIT 1;
SHOW COLUMNS FROM ty;
| 式 | できた列の型 | 読み取れること |
|---|---|---|
IF(1, 1, 'a') |
varchar(2) | 数値と文字列 → 文字列 |
IF(1, 1, 2.5) |
decimal(2,1) | 整数と小数 → DECIMAL |
IF(1, 1, 2e0) |
double | 整数と浮動小数点 → DOUBLE |
IFNULL(1, 'x') |
varchar(2) | 数値と文字列 → 文字列 |
IFNULL(NULL, 1) |
bigint | NULL でない側の型 |
NULLIF(1, 2) |
int | 第1引数の型 |
COALESCE(NULL, 1, 'x') |
varchar(2) | 数値と文字列 → 文字列 |
CASE WHEN 1 THEN 1 ELSE 'x' END |
varchar(2) | 数値と文字列 → 文字列 |
IF(1, NOW(), 'x') |
varchar(19) | 日時と文字列 → 文字列 |
IF(1, CURDATE(), NOW()) |
datetime | DATE と DATETIME → DATETIME |
IFNULL(points, 'なし') |
varchar(11) | INT の列と文字列 → 文字列 |
型の決まり方は MySQL 8.0 リファレンスマニュアルのフロー制御関数のページでも確認しました（IFNULL は文字列・実数・整数の順に優先、NULLIF は第1引数と同じ型）。
文字列になると並び順と比較が変わる
SELECT id, IFNULL(points, 'なし') AS v FROM members ORDER BY v;
+----+--------+
| id | v |
+----+--------+
| 4 | 0 |
| 1 | 1200 |
| 2 | 300 |
| 3 | なし |
| 5 | なし |
+----+--------+
数値の列に文字の既定値を入れると、結果は文字列になり、並び順が「0、1200、300」という文字の順になります。 同じ理由で、IF(1, 2, '10') > IF(1, 10, '2') は文字列どうしの比較になって 1（真）を返しました（数値なら 2 > 10 は 0）。
| 直す前 | 直した後 | 理由 |
|---|---|---|
ORDER BY IFNULL(points, 'なし') |
並べ替えは ORDER BY points、表示だけ IFNULL |
並べ替えは数値のまま行う |
IFNULL(points, '') を計算に使う |
IFNULL(points, 0) |
既定値の型を列に合わせる |
| 小数の桁をそろえたい | IFNULL(5, 0.00) は 5.00 になることを利用、または CAST |
既定値の桁数が結果に影響する |
PostgreSQL・Oracle・SQL Serverでの対応関数一覧
| やりたいこと | MySQL | PostgreSQL | Oracle Database | SQL Server |
|---|---|---|---|---|
| NULL を置き換える（2引数） | IFNULL(x, y) |
COALESCE(x, y) |
NVL(x, y) |
ISNULL(x, y) |
| 最初の NULL でない値 | COALESCE(...) |
COALESCE(...) |
COALESCE(...) |
COALESCE(...) |
| 等しければ NULL | NULLIF(x, y) |
NULLIF(x, y) |
NULLIF(x, y) |
NULLIF(x, y) |
| 2択の分岐 | IF(条件, a, b) |
CASE WHEN 条件 THEN a ELSE b END |
CASE 式（DECODE もある） |
IIF(条件, a, b) または CASE |
| 多分岐 | CASE |
CASE |
CASE |
CASE |
| 条件付きの件数 | SUM(条件) / COUNT(CASE ...) |
COUNT(*) FILTER (WHERE 条件) / COUNT(CASE ...) |
COUNT(CASE ...) |
COUNT(CASE ...) |
| NULL かどうかを 1 / 0 で返す | ISNULL(x) |
x IS NULL |
x IS NULL を CASE で |
x IS NULL を CASE で |
MySQL と PostgreSQL の列は実行して確認しました。Oracle Database と SQL Server の列は未実行で、一般に知られた仕様に基づく記載です。
確認できたことは次のとおりです。
- MySQL 8.0.46 で
NVL(points, 0)を実行するとERROR 1305 (42000): FUNCTION t254.NVL does not existになりました。 - MySQL の
ISNULLは引数が1つの関数です（ISNULL(NULL)は 1、ISNULL(1)は 0）。SQL Server の感覚でISNULL(points, 0)と書くとERROR 1582 (42000): Incorrect parameter count in the call to native function 'ISNULL'になりました。 - PostgreSQL 16.15 では
COALESCE(null, 0)、NULLIF(0, 0)、CASE、10 / NULLIF(0, 0)、COUNT(*) FILTER (WHERE ...)が動きました。IFNULL(null, 0)はERROR: function ifnull(unknown, integer) does not exist、IF(1>0,'a','b')はERROR: function if(boolean, unknown, unknown) does not existでした。 - PostgreSQL は型の混在に厳密で、
COALESCE(null, 1, 'x')はERROR: invalid input syntax for type integer: "x"になりました。MySQL では文字列にそろえて動く式でも、型を合わせてから移す必要があります。
複数のデータベースで使う SQL は、CASE・COALESCE・NULLIF の3つで書いておくと、書き換えが少なくて済みます。
WHERE句で使うときはインデックスに注意する
列を関数で包むと、その列のインデックスによる絞り込みが使われにくくなります。10万行のテーブルで実行計画を比べました。
| WHERE 句 | EXPLAIN FORMAT=TREE の要点 |
|---|---|
IFNULL(points, 0) = 5 |
Index scan on big using points（rows=100338）＋ Filter。インデックス全体を読む |
points = 5 |
Covering index lookup on big using points (points=5)（rows=110） |
points IS NULL OR points = 0 |
Covering index lookup on big using points (points=0 or NULL) |
関数インデックス ((IFNULL(points,0))) を追加後の IFNULL(points, 0) = 5 |
Index lookup on big using idx_f（rows=110） |
WHERE 句では、IFNULL で包む代わりに 列 IS NULL OR 列 = 値 と書くと、通常のインデックスで絞り込めます。 式のまま検索したい場合は、関数インデックスを作る方法もあります。
ALTER TABLE big ADD INDEX idx_f ((IFNULL(points, 0)));
よくある質問
Q. IFNULL と COALESCE はどちらを使えばよいですか？ A. 引数が2つなら結果は同じです。MySQL だけで使う短い SQL なら IFNULL、候補が3つ以上あるときや他のデータベースでも使う SQL なら COALESCE が向いています。
Q. IF と CASE はどう使い分けますか？ A. 2択なら IF、3択以上や NULL を区別したいときは CASE が読みやすくなります。IF は条件が NULL のとき偽の側を返す点を覚えておくと、結果を読み違えません。
Q. IFNULL と NULLIF の違いは何ですか？
A. 向きが逆です。IFNULL は「NULL を別の値にする」、NULLIF は「指定した値を NULL にする」関数です。IFNULL(NULLIF(col, ''), '既定値') のように組み合わせると、空文字と NULL をまとめて置き換えられます。
Q. COUNT(IF(条件, 1, 0)) の結果が全件数になります。
A. COUNT は NULL 以外をすべて数えるため、0 も数えられます。COUNT(IF(条件, 1, NULL)) か SUM(IF(条件, 1, 0)) に書き換えてください。
まとめ
- IF は2択、CASE は多分岐、IFNULL と COALESCE は NULL の置き換え、NULLIF は値を NULL に変える。
- IF は条件が NULL のとき偽の側を返す。NULL を区別するなら
IS NULLを先に判定するか CASE を使う。 - 条件付き集計は
SUM(IF(条件,1,0))かCOUNT(CASE WHEN 条件 THEN 1 END)。COUNT に 0 を渡すと全行が数えられる。 - ゼロ除算は
a / NULLIF(b, 0)で避ける。INSERT・UPDATE での ERROR 1365 も防げる。 - 空文字と NULL は
IFNULL(NULLIF(列, ''), 既定値)でまとめて扱える。 - UPDATE の CASE には ELSE か WHERE を付ける。付けないと対象外の行が NULL になる。
- 型の違う値を混ぜると文字列になり、並び順が変わる。他の製品でも使うなら CASE・COALESCE・NULLIF で書く。
株式会社RJC ― SI事業・SES事業・AI駆動開発。RJCは一緒に成長を楽しめる会社です。
WE ARE HIRING
RJCで一緒に開発しながら、
成長を楽しみませんか？
RJCは、Web・モバイル・AIを活用した開発プロジェクトで、テックリードやPM・PMOも活躍するシステム開発会社です。会社を知る、待遇を確かめる、話を聞いてみる。気になるところ見てみてください！
- 127日年間休日
- 12時間平均残業時間
- 毎日ガチャ遊びココロも大切にする福利厚生。アマギフなどの賞品ラインナップ！
ほかにも、チケットレストラン、書籍読み放題、2年ごとの慰労報奨（休暇 or 金一封）、11期連続の黒字決算。
