SQL: COALESCE() 関数で NULL を別の値に置き換える

RDB のスキーマ設計において、なるべくなら Nullable なカラムは無い方が良いけど、そうもいかないときがある。そんなときは COALESCE() 関数を使うと NULL になっているフィールドを別の値に置換できる。これは、特定の値を NULL に置換できる NULLIF() 関数と対になるものかな。

blog.amedama.jp

動作確認に使った環境は次の通り。

$ sw_vers 
ProductName:    Mac OS X
ProductVersion: 10.12.5
BuildVersion:   16F73
$ mysql --version
mysql  Ver 15.1 Distrib 10.2.6-MariaDB, for osx10.12 (x86_64) using readline 5.1

下準備

下準備として、例えば次のようなテーブルを作っておく。何らかの商品の購入履歴のテーブルで、元々の金額といくらディスカウントがあったかを記録する。

> CREATE TABLE purchases (
    ->   id INTEGER,
    ->   amount INTEGER,
    ->   discount INTEGER,
    ->   PRIMARY KEY (id)
    -> );
Query OK, 0 rows affected (0.02 sec)

サンプルとなるレコードをいくつか追加しておく。ここでディスカウントの金額が NULL になっているものがあるところに注意する。ディスカウントがなかったときは 0 ではなく NULL が入るということ。

> INSERT INTO
    ->   purchases
    -> VALUES
    ->   (1, 1000, NULL),
    ->   (2, 2000, 500),
    ->   (3, 3000, 1000);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

格納されるデータはこんな感じ。

> SELECT * FROM purchases;
+----+--------+----------+
| id | amount | discount |
+----+--------+----------+
|  1 |   1000 |     NULL |
|  2 |   2000 |      500 |
|  3 |   3000 |     1000 |
+----+--------+----------+
3 rows in set (0.00 sec)

NULL を置換せずに計算した場合

何も考えずにディスカウントが適用された金額を計算してみよう。これは、ただ単に金額からディスカウントを引くだけ。

> SELECT
    ->   id,
    ->   amount - discount AS discounted_amount
    -> FROM purchases;
+----+-------------------+
| id | discounted_amount |
+----+-------------------+
|  1 |              NULL |
|  2 |              1500 |
|  3 |              2000 |
+----+-------------------+
3 rows in set (0.00 sec)

するとディスカウントの値が NULL だったところの内容が NULL になってしまった。これは NULL の伝搬と呼ばれるもので、計算内容に NULL が混入していると結果が全て NULL になってしまうというもの。

COALESCE() 関数で NULL を置換する

この NULL の伝搬を防ぐには NULL になっている値を何らかの値に置換してやれば良い。今回の例であれば NULL を 0 に置き換えてしまえば大丈夫なはず。そんなとき使えるのが今回紹介する COALESCE() 関数で、これは第一引数が NULL だったときに第二引数の値に置換できる。

先ほどのクエリに COALESCE() 関数を使ってみよう。

> SELECT
    ->   id,
    ->   amount - COALESCE(discount, 0) AS discounted_amount
    -> FROM purchases;
+----+-------------------+
| id | discounted_amount |
+----+-------------------+
|  1 |              1000 |
|  2 |              1500 |
|  3 |              2000 |
+----+-------------------+
3 rows in set (0.00 sec)