SQLのcoalesce関数についてまとめています。
coalesceは、NULLの代替値を取得する関数です。データベースごとに多少の差異があります。
Coalesce関数の構文
coalesce関数は、対象の値がNULLだった場合、代替として使う値を返す関数です。下記の書式では、対象の値がNULLだった場合、代わりに評価する値をカンマで区切って指定します。
coalesce( col1, col2, col3, '', … )
MySQLのcoaleceのサンプルSQL
MySQLで、coalesceの動きを確認してみましょう。
指定した値を順番に評価し、NULL以外の値を返します。全てNULLだった場合は、NULLを返します。
mysql> SELECT COALESCE(NULL,1);
-> 1
mysql> SELECT COALESCE(NULL,NULL,NULL);
-> NULL
以下のSQLは、salaries（年収）テーブルから、birth_date（誕生日）の年ごとにsalary（年収）の合計を出力します。salaryの値がNULLの場合は、coalesceで0に置き換えています。
select Date_format(birth_date, '%Y') as year,
Sum(COALESCE(salary,0))
from employees
inner join salaries
on salaries.emp_no = employees.emp_no
inner join dept_emp
on employees.emp_no = dept_emp.emp_no
inner join departments
on dept_emp.dept_no = departments.dept_no
where salaries.to_date = '9999-01-01'
and dept_name = 'Sales'
group by Date_format(birth_date, '%Y')
実行結果はこうなります。
+------+-------------------------+ | year | Sum(COALESCE(salary,0)) | +------+-------------------------+ | 1952 | 266268511 | | 1953 | 279889946 | | 1954 | 284458184 | | 1955 | 292812067 | | 1956 | 288620912 | | 1957 | 284076293 | | 1958 | 294605154 | | 1959 | 289944673 | | 1960 | 281743172 | | 1961 | 282715210 | | 1962 | 290871654 | | 1963 | 289909858 | | 1964 | 281566480 | | 1965 | 23888563 | +------+-------------------------+ 14 rows in set (0.73 sec)
【関連記事】
▶MySQLのNULL判定はどうやる？ 除外判定やNULLのSUMを取得するには？
OracleのcoalesceのサンプルSQL
以下のSQLは、product_informationテーブルから、supplier_id=102050のデータのproduct_id、list_price、min_price、Saleを取得します。Saleの値は、0.9*list_price、min_price、5のうち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
なお、Oracleには引数２つのみで簡易的に使えるNVL関数も用意されています。
SQL Serverのcoalesce CASEで置き換える例
以下のSQLは、Production.Productテーブルから、Name,Class,Color、ProductNumber、FirstNotNullを取得します。FirstNotNullには、Class,Color、ProductNumberのうち、NULLではない値が表示されます。
SELECT Name, Class, Color, ProductNumber, COALESCE(Class, Color, ProductNumber) AS FirstNotNull FROM Production.Product;
参考）COALESCE (Transact-SQL) – SQL Server | Microsoft Docs
coalesceは、CASEで置き換えることも可能です。
CASE WHEN (expression1 IS NOT NULL) THEN expression1 WHEN (expression2 IS NOT NULL) THEN expression2 ... ELSE expressionN END Name Color ProductNumber FirstNotNull ------------ ---------- ------------- ------------ Socks, Mens NULL PN1278 PN1278 Socks, Mens Blue PN1965 Blue NULL White PN9876 White
PostgreSQL coalesceのほかに、NULLIF関数あり
SELECT COALESCE(description, short_description, '(none)')
PostgreSQLには、NULLIFという関数が用意されています。
下記のように記述すると、value1が(none)の場合にNULL、そうでなければvalue1を返します。
dvdrental=# select NULLIF('a','(none)');
nullif
--------
a
(1 row)
dvdrental=# select NULLIF(NULL,'(none)');
nullif
--------
(1 row)
まとめ
- coalsceは、指定した値がNULLだった場合、代替の値を返す関数。
- 指定した値が全てNULLだった場合は、NULLを返す。
- データベースによっては、OracleのNVLや、PostgreSQLのNULLIFなど簡易的なcoalsceとして使える関数が用意されている。
