SQLのcoalesceについてまとめています。


coalesceは、NULLの代替値を返す関数
- mysql> SELECT COALESCE(NULL,1);
- -> 1
- mysql> SELECT COALESCE(NULL,NULL,NULL);
- -> NULL
coalenceは指定した値のうち、NULLではない最初の値を返す関数で、指定カラムがNULLでも、NULLを返したくない場合などに使います。
- SELECT product_id, list_price, min_price,
- COALESCE(0.9*list_price, min_price, 5) "Sale"
- FROM product_information
- WHERE supplier_id = 102050
- ORDER BY product_id;
- PRODUCT_ID LIST_PRICE MIN_PRICE Sale
- ---------- ---------- ---------- ----------
- 1769 48 43.2
- 1770 73 73
- 2378 305 247 274.5
- 2382 850 731 765
- 3355 5
なお、データベースによってはcoalesceの簡易版のNVL(oracle)やNULLIF(PostgreSQL)といった関数が用意されています。
【関連記事】
▶SQL coalesce 値がNULLの場合の代替値を返す 簡易版のNVLやNULLIFが使えるDBもあり
coalesceの使い方
coalesceでサブクエリを使うには、サブクエリに括弧をつける
coaleseの引数にはサブクエリを使うことが可能です。そのまま記述するとsyntaxエラーになるので、サブクエリ部分を括弧でくくりましょう。
- mysql> select coalesce( select NULL, 0 ) as value;
- ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'select NULL, 0 ) as value' at line 1
- #サブクエリにカッコをつけると、syntaxエラーが解消される
- mysql> select coalesce( (select NULL), 0 ) as value;
- +-------+
- | value |
- +-------+
- | 0 |
- +-------+
- 1 row in set (0.00 sec)
関連)SQL関数「COALESCE」でサブクエリを使うにはひと工夫が必要 – ソフラボの技術ブログ
coaleceはcase式で置き換えが可能
coaleceはcase式による置き換えが可能です。下記は、first_name(姓名の名)がNULLの場合のみ”no_data”を返すSQLです。
- mysql> select * from employees_copy limit 10;
- +--------+------------+--------------+-----------+--------+------------+
- | emp_no | birth_date | first_name | last_name | gender | hire_date |
- +--------+------------+--------------+-----------+--------+------------+
- | 10001 | 1953-09-02 | | Facello | M | 1986-06-26 |
- | 10002 | 1964-06-02 | | Simmel | F | 1985-11-21 |
- | 10003 | 1959-12-03 | | Bamford | M | 1986-08-28 |
- | 10004 | 1954-05-01 | Chirstian | Koblick | M | 1986-12-01 |
- | 10005 | 1955-01-21 | NULL | Maliniak | M | 1989-09-12 |
- | 10006 | 1953-04-20 | Anneke | Preusig | F | 1989-06-02 |
- | 10007 | 1957-05-23 | Tzvetan | Zielinski | F | 1989-02-10 |
- | 10008 | 1958-02-19 | Saniya | Kalloufi | M | 1994-09-15 |
- | 10009 | 1952-04-19 | Sumant | Peac | F | 1985-02-18 |
- | 10010 | 1963-06-01 | Duangkaew | Piveteau | F | 1989-08-24 |
- +--------+------------+--------------+-----------+--------+------------+
- 10 rows in set (0.05 sec)
- #NULLの項目だけno dataに変換される
- mysql> select coalesce(first_name, "no data") from employees_copy limit 10;
- +---------------------------------+
- | coalesce(first_name, "no data") |
- +---------------------------------+
- | |
- | |
- | |
- | Chirstian |
- | no data |
- | Anneke |
- | Tzvetan |
- | Saniya |
- | Sumant |
- | Duangkaew |
- +---------------------------------+
- 10 rows in set (0.00 sec)
空白や、1文字目が空白のものはそのまま出力され、NULLの箇所のみno dataという表示に置き換わっています。
case式で置き換えるとこうなります。
- mysql>SELECT
- CASE
- WHEN first_name IS NULL THEN "no data"
- ELSE first_name
- END
- FROM employees_copy limit 10;
- +-----------------------------------------------------------------+
- | case when first_name is null then "no data" else first_name end |
- +-----------------------------------------------------------------+
- | |
- | |
- | |
- | Chirstian |
- | no data |
- | Anneke |
- | Tzvetan |
- | Saniya |
- | Sumant |
- | Duangkaew |
- +-----------------------------------------------------------------+
【関連記事】
▶SQLのCASE式サンプル集 order byやgroup byとの組み合わせもバッチリ


まとめ

- coalesceは、NULL値の代替を返す関数
- coalesceの引数にサブクエリを使うときは、サブクエリ部分を括弧でくくる
- coalsceはcase式で置き換えが可能