【SQL】NULL値を制御/SQLマスターへの道「COALESCE」

導入

SQL文でNULL値を扱う際の便利な関数、COALESCEを紹介しようと思います。

• SELECT句で、NULL値を置き換えることで、データの可読性を高めることができたり。
• ORDER BY句で、NULL値のソートの条件分岐の複雑性を吸収したり。

と、SQL文の簡略化にぴったりです。

今回の記事では、簡単にCOALESCE関数の説明と実践例を2つご紹介します。

COALESCEについて

リストの最初の非 NULL 値を返します。非 NULL 値がない場合は、NULL を返します。

つまり、欠損値(NULL)にデフォルト値を指定することができます。

例

SELECT COALESCE(`office`.`locale`, `office`.name`, `リモート勤務`);

上記のクエリを例にすると...。
office.locale(オフィスの場所)を出力。
office.localeが空の場合、office.name(オフィス名)を出力。
office.nameが空の場合、リモート勤務と出力します。

これだけでもだいぶ便利そうですよね。

ポケモンを例に実践してみます。

実践① - たねポケモンを一覧でわかりやすく表示

一覧内でたねポケモンかどうかを判別したい時、恐らくありますよね。

COALESCEを用いて、SELECT句内で「たねポケモン」表示をしてみましょう。

サンプルデータ

ID	名前	タイプ	進化前(1)	進化前(2)
1	フシギダネ	くさ・どく	NULL	NULL
2	フシギソウ	くさ・どく	1	NULL
3	フシギバナ	くさ・どく	2	1
4	ヒトカゲ	ほのお	NULL	NULL
5	リザード	ほのお	4	NULL
6	リザードン	ほのお	5	4
7	ゼニガメ	みず	NULL	NULL
8	カメール	みず	7	NULL
9	カメックス	みず	8	7

Query

SELECT `POKEMON`.`ID`, `POKEMON`.`名前`, COALESCE(`POKEMON`.`進化前(1)`, `進化なし`) AS `進化前のポケモンID`
FROM POKEMON;

解説

ちょっと雑ですがこんな感じですかね。
進化前のポケモンが存在する場合は、IDを表示。なければ「たねポケモン」と表示させるようにしてみました。

結果

ID	名前	タイプ	進化前のポケモンID
1	フシギダネ	くさ・どく	たねポケモン
2	フシギソウ	くさ・どく	1
3	フシギバナ	くさ・どく	2
4	ヒトカゲ	ほのお	たねポケモン
5	リザード	ほのお	4
6	リザードン	ほのお	5
7	ゼニガメ	みず	たねポケモン
8	カメール	みず	7
9	カメックス	みず	8

実践② - 最終進化のポケモンのソートキーを制御

最終進化後のポケモンのソートを軽くして、たねポケモンと1段階進化後のポケモンのソートを重くしたい...と思いました。
(要件が思いつかないので多少強引です。)

先ほどと同じテーブルを使います。

サンプルデータ

ID	名前	タイプ	進化前(1)	進化前(2)
1	フシギダネ	くさ・どく	NULL	NULL
2	フシギソウ	くさ・どく	1	NULL
3	フシギバナ	くさ・どく	2	1
4	ヒトカゲ	ほのお	NULL	NULL
5	リザード	ほのお	4	NULL
6	リザードン	ほのお	5	4
7	ゼニガメ	みず	NULL	NULL
8	カメール	みず	7	NULL
9	カメックス	みず	8	7

Query

SELECT `POKEMON`.`ID`, `POKEMON`.`名前`
FROM POKEMON
ORDER BY COALESCE(`POKEMON`.`進化前(2)`, `2147483647`) ASC, `POKEMON`.`ID` ASC

解説

1. 2進化前のポケモンのIDでソートを行う
2. 2進化前のポケモンが存在しなければソートを重くし、IDでのソートを行う

※ MySQLではINT型の最大値は2,147,483,647です。

結果

ID	名前
3	フシギバナ
6	リザードン
9	カメックス
1	フシギダネ
2	フシギソウ
4	ヒトカゲ
5	リザード
7	ゼニガメ
8	カメール

これで、最終進化後(2進化)のポケモンのソートを軽くしたテーブルを出力することができましたね。

まとめ

今回は、COALESCE関数を使ってNULL値の扱いについて触れてみました。

ちなみに...。

WHERE句でも良い判定方法ができるかなと調べてみましたが、IS NOT NULLの方が楽ですよね。

SELECT * FROM `テーブル名` WHERE `フィールド名` IS NOT NULL;

※ NULL以外を出力。