COALESCEが予想外の動きをしていたことで、とある調査に4時間程度かかったので
将来同じようなSQLに遭遇したときに3秒で解決するために＆いつかの誰かのために記録しておきます。
読み方（いつもわからなくなるので）
コウアレス。
使い方
COALESCE（引数1,引数2,…）
引数の数は任意で、引数の最初の非NULL値を返します。
以下では例として単純に2つの引数を指定した場合について書いています。
すなわち、引数1がNULLだったら引数2を返す、引数1がNULL以外だったら引数1を返す。
例1：引数1がNULLではない
SELECT COALESCE('0123', 'BBB') FROM DUAL
- - - - -
'0123'
例2：引数1がNULL
SELECT COALESCE(null, 'BBB') FROM DUAL
- - - - -
'BBB'
通常はリテラルだけを引数にする使い方はしません。
抽出対象のカラムを指定して、OracleでいうNVL、SQLServerでいうISNULLのような使い方をするのがほとんどかと思います。
気を付けるべき点
引数1と引数2は同じデータ型とするのが原則。
異なるデータ型を指定した場合、DBエンジンやバージョンによって挙動が異なる。
Teradataやsqliteの場合
SELECT COALESCE('0123', -1)
- - - - -
'0123'
Db2 Warehouse on Cloudの場合
(バージョンを忘れてしまった…。Db2では再現しなかった)
SELECT COALESCE('AAA', -1) FROM SYSIBM.DUAL
→エラーになり参照できない
しかしSELECTではエラーになるがSELECT結果をINSERTするSQLでは失敗せず、
引数1がNULL以外の場合、引数2で指定したデータ型で引数1が返される。
INSERT INTO TABLE_1 (COL_1)
SELECT COALESCE('0123', -1) FROM SYSIBM.DUAL
SELECT * FROM TABLE_1
COL_1
- - - - -
123
引数2が数値のため'0123'が暗黙的に数値型にキャストされ、
その結果前ゼロが消失した数値123が返されていたということ。
引数1が文字列で引数2が数値という場合には上記のようなケースもありとくに注意が必要。
その他の製品や具体的なバージョンでの挙動の違いはわからないけど（ここでは整理しないけど）、
こういうことが起きるということを覚えているだけでも意味があると思うので、
忘れないようにしておこう。