受講料が最大70%OFF 受講料が最大70%OFF

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のCOALESCE

なお、Oracleには引数2つのみで簡易的に使える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の条件式

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として使える関数が用意されている。

エンジニアになりたい人に選ばれるプログラミングスクール「ポテパンキャンプ 」

ポテパンキャンプは卒業生の多くがWebエンジニアとして活躍している実践型プログラミングスクールです。 1000名以上が受講しており、その多くが上場企業、ベンチャー企業のWebエンジニアとして活躍しています。

基礎的な学習だけで満足せず、実際にプログラミングを覚えて実践で使えるレベルまで学習したいという方に人気です。 プログラミングを学習し実践で使うには様々な要素が必要です。

それがマルっと詰まっているポテパンキャンプでプログラミングを学習してみませんか?

卒業生の多くがWebエンジニアとして活躍

卒業生の多くがWeb企業で活躍しております。
実践的なカリキュラムをこなしているからこそ現場でも戦力となっております。
活躍する卒業生のインタビューもございますので是非御覧ください。

経験豊富なエンジニア陣が直接指導

実践的なカリキュラムと経験豊富なエンジニアが直接指導にあたります。
有名企業のエンジニアも多数在籍し品質高いWebアプリケーションを作れるようサポートします。

満足度高くコスパの高いプログラミングスクール「ポテパンキャンプ」

運営する株式会社ポテパンは10,000人以上のエンジニアのキャリアサポートを行ってきております。
そのノウハウを活かして実践的なカリキュラムを随時アップデートしております。

代表の宮崎もプログラミングを覚えサイトを作りポテパンを創業しました。
本気でプログラミングを身につけたいという方にコスパ良く受講していただきたいと思っておりますので、気になる方はぜひスクール詳細をのぞいてくださいませ。