Oracle SQL 関数 : COALESCE : 指定リストのNULL以外判定 

　numberRet := COALESCE( expr1, expr2, ... , exprN );

　COALESCE 関数は指定された式リスト expr1...N を順に判定し NULL ではない式の値を返します。
  式リストが全て NULL の場合は NULL を返します。

以下に COALESCE 関数の例を示します。

ソース表示
プリンタSyntaxHighlighter version 2.1.364
SQL> SET NULL '<NULL>'
SQL> COL ALLNULL FORMAT A10
SQL> SELECT
  2    COALESCE( NULL, NULL, '/TEST1/' ) AS "TEST1"
  3   ,COALESCE( NULL, '/TEST2/', NULL, '/TEST22/' ) AS "TEST2"
  4   ,COALESCE( NULL, NULL, NULL ) "ALLNULL"
  5  FROM DUAL;
 
TEST1          TEST2          ALLNULL
-------------- -------------- ----------
/TEST1/        /TEST2/        <NULL>


上記の SET NULL コマンドはデータが NULL の場合に代替して表示する文字列を設定します。
また COL[UMN] コマンドは指定したカラム名の表示形式を指定します。 上記の場合は「ALLNULL」と言うカラム名の場合に半角文字数10桁で表示する設定です。

なお COALESCE の引数が1個しかない場合は以下の様にエラーが発生します。

ソース表示
プリンタSyntaxHighlighter version 2.1.364
SQL> SELECT
  2   COALESCE( NULL ) AS "ALLNULL"
  3  FROM DUAL;
 COALESCE( NULL ) AS "ALLNULL"
 *
行2でエラーが発生しました。:
ORA-00938: 関数の引数が不足しています。


引き数の式リストはリテラル（直値）だけではなく、テーブルのカラムも指定できます。
以下は、テーブルを仮想的に「DUAL」表から4行の各種データを生成したものを COALESCE 関数で扱っています。
当然、リアルなテーブルのカラムでも指定可能です。

ソース表示
プリンタSyntaxHighlighter version 2.1.364
SQL> SELECT
  2   NAME
  3  ,COALESCE(VAL1, VAl2, VAL3)
  4  FROM (
  5    SELECT 100  AS VAL1, 200  AS VAL2, 300  AS VAL3, '1st'  AS NAME FROM DUAL
  6    UNION
  7    SELECT NULL AS VAL1, 2000 AS VAL2, 300  AS VAL3, '2nd'  AS NAME FROM DUAL
  8    UNION
  9    SELECT NULL AS VAL1, NULL AS VAL2, 3000 AS VAL3, '3rd'  AS NAME FROM DUAL
 10    UNION
 11    SELECT NULL AS VAL1, NULL AS VAL2, NULL AS VAL3, 'null' AS NAME FROM DUAL
 12  ) TX;
 
NAME     COALESCE(VAL1,VAL2,VAL3)
-------- ------------------------
1st                           100
2nd                          2000
3rd                          3000
null     <NULL>


COALESCE 関数の使い道ですが、テーブル上の複数のカラムの中で NULL ではないカラムのデータを対象としたい場合に使えそうです。


share


スッキリわかるSQL入門 第3版 ドリル256問付き！ スッキリわかるシリーズ Kindle版

本書用のWebアプリを使ってSQLを実行し学習していきます。初心者の方に分かりやすく、図表を多く使って解説されています。 学習の最後には200問越えのドリルが付いていてSQLのスキルが試せる様になっています。