PostgreSQL COALESCE関数を使ってSELECT文でデフォルト値を返す

2020年4月14日2023年5月28日

Contents

1. 検索条件にマッチするデータが存在しない場合にデフォルト値を返す
2. COALESCE関数を利用してデフォルト値を返す
3. 参考情報

検索条件にマッチするデータが存在しない場合にデフォルト値を返す

以下の国ごとの送料を管理するテーブルを考えます。国コード「XX」のレコードは指定された国がUS(アメリカ)、JP(日本)以外の国の場合に適用される送料とします。例えば、CN(中国)が指定された場合の送料は999円となります。

国コード	送料(円)
US	100
JP	101
XX	999

国別の送料テーブル

テーブル作成とデータ投入を以下のSQLで行います。

-- テーブル作成
create table postage_table (
    id serial primary key,
    country_code varchar(2) not null,
    postage int default 0
);

-- データ投入
insert into postage_table (country_code, postage)
values
('US', 100),
('JP', 101),
('XX', 999);

国コードを指定してデータを取得するSQLは一番単純に考えると以下のようになると思います。

select * from postage_table where country_code = 'JP';

id | country_code | postage
----+--------------+---------
  2 | JP           |     101

このSQLでCN(中国)で検索すると当然値は取得できません。この場合、SQLを発行するアプリケーション側で検索対象の国コードがUSまたはJPでないときは国コードをXXに変換してSQLを発行することになります。

-- country_codeがCNのデータは存在しない
select * from postage_table where country_code = 'CN';

 id | country_code | postage
----+--------------+---------
(0 rows)

-- country_codeをXXに変換してSQLを発行
select * from postage_table where country_code = 'XX';
id | country_code | postage
----+--------------+---------
  3 | XX           |     999

国コードがUS, JP 以外の場合にXXのレコードを返すSQLを考えます。

COALESCE関数を利用してデフォルト値を返す

COALESCE関数は与えられた引数の中でNULLではない最初の引数を返します。全ての引数がNULLの場合はNULLを返します。実際に実行すると以下のような結果になります。

select coalesce(0, 1);
 coalesce
----------
        0
(1 row)

select coalesce(null, 1);
 coalesce
----------
        1
(1 row)

select coalesce(null, null);
 coalesce
----------

(1 row)

COALESCE関数の第１引数をSELECT 文による検索結果、第２引数をデフォルト値にしたい値とすることでSELECT 文での検索結果が０件の場合にデフォルト値ことができます。

以下のようにSQLを記述することでUS, JP以外の国コードをXXに変換できます。WHERE句の条件にマッチするレコードが存在しない場合の検索結果をnullとするためにmax関数を使用しています。MAX関数は渡される引数が0件場合はnullを返します。

select coalesce(max(country_code), 'XX') from postage_table where country_code = 'JP';
 coalesce
----------
 JP
(1 row)

select coalesce(max(country_code), 'XX') from postage_table where country_code = 'US';
 coalesce
----------
 US
(1 row)

select coalesce(max(country_code), 'XX') from postage_table where country_code = 'CN';
 coalesce
----------
 XX
(1 row)

この国コードを変換するSQLをWHERE句に指定することで国コードがUS, JP以外の場合に送料999円を返すSQLを書くことができます。

select * from postage_table where country_code = 
( select coalesce(max(country_code), 'XX') from price_table where country_code = 'JP');
id | country_code | postage
----+--------------+---------
  2 | JP           |     101
(1 row)

select * from postage_table where country_code = 
( select coalesce(max(country_code), 'XX') from price_table where country_code = 'US');
 id | country_code | postage
----+--------------+---------
  1 | US           |     100
(1 row)

select * from postage_table where country_code = 
( select coalesce(max(country_code), 'XX') from price_table where country_code = 'CN');
 id | country_code | postage
----+--------------+---------
  3 | XX           |     999
(1 row)

参考情報

PostgreSQL,SQL

Posted by fanfanta