COALESCE()で Null を変換してみよう!SQL超初心者の勉強

nullを置換したい時があると思います。PostgreSQLでnullを変換するのにはCOALESCE()を使います。
ちなみにCOALESCEの読み方はコアレスです。

動作環境

SQL FIDDLEでMySQL5.6、PostgreSQL9.6で動作確認しました。
下記文章は主にPostgreSQLで仕様で調べています。

COALESCE()とは

COALESCE()とは、NULLでない最初の引数を返します。
全ての引数がNULLの場合にはNULLが返されます。

主にnullを変換することに使われています。

構文

COALESCE(value [, …])

よく使う書き方に置き換えると以下のようになります。

COALESCE(value, 'nullを変換したい値')

実際に書いてみよう!

実際にサンプルコードで動きを見ながら使い方を確認してみましょう。

準備

以下のテーブルを使用してサンプルコードを作成します。

temidtittleepisode
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話
3null変換第3話
4リーガルハイnull変換
5null変換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スペルが難しいですよね。