SQL coalesceの引数にサブクエリが使用可能。case式で置き換えできる

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式で置き換えが可能