皆さんはNULLの扱いで困ったことは無いでしょうか？ NULLは何も無いことを示すフラグのようなもので、実体が無いため単純に比較することができないため、IS NULLやIS NOT NULLを使って対応する必要がありちょっぴり面倒です。またSUMやAVG等で予期せずNULLが帰ってくるパターンなどもあるため少し厄介です。
そんな時に便利に扱えるのが、今回紹介するCOALESCE関数です。
検証環境
今回はdockerで建てたMySQLを使用します。以下のコマンドでdockerを建ててローカルからアクセスをします。
% docker run --platform linux/x86_64 -p 3307:3306 -e MYSQL_ROOT_PASSWORD=my-secret-pw -d mysql:latest
アクセス方法は以下の通りになります。
% mysql -uroot -pmy-secret-pw -h127.0.0.1 -P3307
執筆時点では、以下の通りMySQL 8.
mysql> select version(); +-----------+ | version() | +-----------+ | 8.0.31 | +-----------+ 1 row in set (0.01 sec)
今回はtestデータベースとdatasテーブルを作成します。
mysql> create test; mysql> CREATE TABLE `datas` (c1 int, c2 real, c3 varchar(2));
COALESCE関数を使ってみる
さて、COALESCE関数を早速使ってみましょう。使い方は簡単です。関数の引数として値の候補をリストとして渡します。まずはわかりやすく直値を入れて確かめてみましょう。
mysql> SELECT COALESCE(NULL); +----------------+ | COALESCE(NULL) | +----------------+ | NULL | +----------------+ 1 row in set (0.01 sec)
NULLだけを入れた場合には上記のようにNULLが返ってきます。続いて、NULLと1を入れた場合どういう挙動になるか確認してみましょう。
mysql> SELECT COALESCE(NULL,1); +------------------+ | COALESCE(NULL,1) | +------------------+ | 1 | +------------------+ 1 row in set (0.01 sec)
1が返ってきました。続いて、その後ろに2を追加したらどうなるか試してみましょう。
mysql> SELECT COALESCE(NULL,1,2); +--------------------+ | COALESCE(NULL,1,2) | +--------------------+ | 1 | +--------------------+ 1 row in set (0.00 sec)
この場合も1が返りました。このように先頭から確認していって、最初にNULL以外のものを発見した場合に返すという関数になります。
もちろん直値以外のカラムでも使用することができます。
COALESCE関数が役に立つ場合
さて、COALESCE関数ですが、うちはNOT NULL制約を適切に付けているからNULLは基本的には出ないので問題ないよ、と思っているかもしれませんが、いくつか例外があると思います。
そのうちの1つですが、AVGやSUM関数などは、中身が空、もしくは全ての行がNULLの場合、NULLを返してきます。
本当かどうか確認してみましょう。
mysql> SELECT SUM(c1) FROM datas; +---------+ | SUM(c1) | +---------+ | NULL | +---------+ 1 row in set (0.01 sec)
まず中身が無いことが確認できました。続いて、実際に結果がどうなるのか試してみましょう。
mysql> SELECT SUM(c1), AVG(c1), COUNT(c1) FROM datas; mysql> SELECT SUM(c1), AVG(c1), COUNT(c1) FROM datas; +---------+---------+-----------+ | SUM(c1) | AVG(c1) | COUNT(c1) | +---------+---------+-----------+ | NULL | NULL | 0 | +---------+---------+-----------+ 1 row in set (0.02 sec)
このようにSUMとAVGはNULLに、COUNTは0件が返りました。COUNTは少し特殊なので、同じ感覚で空のテーブルにSUMやAVGを実行すると、思わぬ所でNULLが飛び出してくるなんてこともあります。
ここで特に注意したいのが、NULLと演算を行うとNULLになってしまうという特性です。
mysql> SELECT SUM(c1) + 1 FROM datas; +-------------+ | SUM(c1) + 1 | +-------------+ | NULL | +-------------+ 1 row in set (0.00 sec)
別のテーブルで計算した値と今回のSUM(c1)を、万が一混ぜて計算して出力しようとした時に、結果としてNULLが出力されることになります。SQLで集計などを取る場合は、値が入っていない状態でも取れるように、COALESCE関数を使って初期値を入れてあげましょう。
mysql> SELECT COALESCE(SUM(c1),0) + 1 FROM datas; +-------------------------+ | COALESCE(SUM(c1),0) + 1 | +-------------------------+ | 1 | +-------------------------+ 1 row in set (0.00 sec)
このように、計算できていることがわかります。ちなみに、今回のように別な型を持つカラムを渡すこともできます。これを確認するために、以下のような値をdatasテーブルに入れます。
mysql> INSERT INTO datas (c1, c2, c3) VALUES (1,NULL,NULL),(NULL,2.0,NULL),(NULL,NULL,'3');
一旦SELECTしてみましょう。
mysql> select * FROM datas; +------+------+------+ | c1 | c2 | c3 | +------+------+------+ | 1 | NULL | NULL | | NULL | 2 | NULL | | NULL | NULL | 3 | +------+------+------+ 3 rows in set (0.01 sec)
1行ごとに各カラムに値が入っていることがわかります。続けて、以下のようなクエリを実行してみましょう。
mysql> SELECT COALESCE(c1, c2, c3) FROM datas; +----------------------+ | COALESCE(c1, c2, c3) | +----------------------+ | 1 | | 2 | | 3 | +----------------------+ 3 rows in set (0.01 sec)
このように、型の異なるカラムでも使用することができます。
IFNULL関数との違い
COALESCE関数と似た機能にIFNULL関数があります。こちらでも、先ほどと同様のクエリを実行してみましょう。
mysql> SELECT IFNULL(SUM(c1),0) + 1 FROM datas; +-----------------------+ | IFNULL(SUM(c1),0) + 1 | +-----------------------+ | 1 | +-----------------------+ 1 row in set (0.01 sec)
COALESCE関数と同様に動いていることがわかります。では、完全に同じ機能でただのエイリアスなのか？
また、COALESCE関数はSQL標準ですが、IFNULL関数はMySQL固有のものとなるため、使用を控えたほうが移行などの際に便利かもしれません。
複数の型を入れた場合の挙動
COALESCE関数やIFNULLを使用した場合、戻り値の型がどうなるか考えてみましょう。
MySQLの公式のドキュメントを確認すると、STRING, REAL, INTEGERの順に評価され、なるべく多くの値が入る最大公約数を選ぶようです。文字列が含まれている場合はSTRINGが選択され、実数が含まれている場合はREALが、整数値のみの場合はINTEGERといった具合に選択されます。こちらCREATE TABLE文を使用して確かめてみましょう。
mysql> CREATE TABLE tmp SELECT COALESCE(1,2.0,'test') AS test; Query OK, 1 row affected (0.07 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> desc tmp; +-------+------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+------------+------+-----+---------+-------+ | test | varchar(4) | NO | | | | +-------+------------+------+-----+---------+-------+ 1 row in set (0.03 sec)
STRING, REAL, INTEGERが存在する場合はSTRINGが選択されていることがわかります。続いて、REALとINTEGERの値にした場合はどうなるでしょうか。
mysql> CREATE TABLE tmp2 SELECT COALESCE(1,2.0) AS test; Query OK, 1 row affected (0.06 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> desc tmp2; +-------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+--------------+------+-----+---------+-------+ | test | decimal(2,1) | NO | | 0.0 | | +-------+--------------+------+-----+---------+-------+ 1 row in set (0.03 sec)
decimal型、つまりはREAL型として登録されていることがわかります。続いて、INTEGERのみを指定した場合どうなるでしょうか。
mysql> CREATE TABLE tmp3 SELECT COALESCE(1) AS test; Query OK, 1 row affected (0.06 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> desc tmp3; +-------+------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+------+------+-----+---------+-------+ | test | int | NO | | 0 | | +-------+------+------+-----+---------+-------+ 1 row in set (0.02 sec)
というようにSTRING, REAL, INTEGERの順に優先されていることがわかりました。戻り値の型が何になるのか直感的にわかりにくいため、別の型のカラムを混ぜないほうが混乱を招かずにすむと思います。特に文字列型と混ぜると、直感に反する動作を起こすと思いますので注意しましょう。
まとめ
今回は、NULL値の扱いが楽になるCOALESCE関数を紹介しました。リストの先頭から評価してNULL以外の値があったらその値を返すという仕組みのため、最後に固定値を入れることができなければ、結局NULLとなってしまうことには注意しましょう。
また、基本的にNULLは厄介なためできる限り正規化を正しく行い、正しく立ち向かいましょう。そのうえで残るものやOUTER JOINした場合、今回例示したようにSUMやAVGなど、仕組み上どうしてもNULLが発生する可能性があるものに、必要に応じてCOALESCE関数を使用しましょう。
プログラムで対応できる場合もあるとは思いますが、こういった関数を知っておくと、SQLだけで対応できることも増えてくると思いますので、知識として覚えておくと良いかもしれません。