nullを置換したい時があると思います。Mysql、PostgreSQLでnullを変換するのにはCOALESCE()を使います。
ちなみにCOALESCEの読み方はコアレスです。
動作環境
SQL FIDDLEでMySQL5.6、PostgreSQL9.6で動作確認しました。
下記文章は主にPostgreSQLで仕様で調べています。
COALESCE()とは
COALESCE()とは、NULLでない最初の引数を返します。
全ての引数がNULLの場合にはNULLが返されます。
主にnullを変換することに使われています。
構文
COALESCE(value [, …])
よく使う書き方に置き換えると以下のようになります。
COALESCE(value, ‘nullを変換したい値’)
実際に書いてみよう！
実際にサンプルコードで動きを見ながら使い方を確認してみましょう。
準備
以下のテーブルを使用してサンプルコードを作成します。
| temid | tittle | episode |
|---|---|---|
| 1 | 家政婦は見た！ | (null) |
| 2 | 金田一少年の事件簿 | 第2話 |
| 3 | (null) | 第3話 |
| 4 | リーガルハイ | (null) |
| 5 | (null) | (null) |
— CREATE文
CREATE TABLE item (
ItemID numeric(8, 0) unique not null,
Tittle character varying (20) ,
episode character varying (20),
primary key(ItemID)
);
— INSERT文
insert into item
values(0001, ‘家政婦は見た！’, null),
(0002, ‘金田一少年の事件簿’, ‘第2話’),
(0003, null, ‘第3話’),
(0004, ‘リーガルハイ’, null),
(0005, null, null)
COALESCE()でnullを変換
SELECT
COALESCE(itemID),
COALESCE(tittle, ‘null変換’),
COALESCE(episode, ‘null変換’)
FROM
item
■ 結果
| COALESCE(itemID) | COALESCE(tittle, ‘null変換’) | COALESCE(episode, ‘null変換’) |
|---|---|---|
| 1 | 家政婦は見た！ | null変換 |
| 2 | 金田一少年の事件簿 | 第2話 |
| 3 | null変換 | 第3話 |
| 4 | リーガルハイ | null変換 |
| 5 | null変換 | null変換 |
nullをnull変換に変換してみました。
すごく不思議だけど、COALESCE(値)だけでも使える。どんな時に使うんだろう。
COALESCE()で複数列がnullだったら変換
SELECT
COALESCE(Tittle, episode, ‘null変換’)
FROM
item
■ 結果
| COALESCE(Tittle, episode, ‘null変換’) |
|---|
| 家政婦は見た！ |
| 金田一少年の事件簿 |
| 第3話 |
| リーガルハイ |
| null変換 |
第一引数のTittleがnullだった場合(値があればその値を返す)、第二引数のepisodeをnullかどうか判定します。そして第二引数のepisodeがnullだった場合、第三引数を返します。
上記例では3番目ばでしかないですが、増やすことも可能です。
おわり
テーブルに新しく列を追加することになった時に、すでに存在する行数に対してデフォルト値を設定する時にCOALESCE()使いました。勉強しておいて助かりました。
COALESCEスペルが難しいですよね。
