ISNULLとCOALESCEは動作が異なる

ISNULLとCOALESCEは動作が異なります。

その違いを解説します。

ISNULLとCOALESCEは動作が異なるので注意が必要
- COALESCEの基本動作
- ISNULLとの違い

ISNULLとCOALESCEは動作が異なるので注意が必要

SQLを見ているとCOALESCEという関数を使用している箇所を見つました。

調べると、引数（カラムや式）を前から順番に解析し「NULL」でないものを発見したらその値を返してくれるようです。

COALESCEの基本動作

COALESCEは引数（カラムや式）を前から順番に解析し「NULL」を返す。

SELECT COALESCE( NULL, NULL, NULL, 'ここまで全部NULL' ) AS COALESCEの戻り値

を実行すると

COALESCEの戻り値
ここまで全部NULL

が返って来ます。

SELECT COALESCE( NULL, 'ZZZ', NULL, 'ここまで全部NULL' ) AS COALESCEの戻り値

を実行すると最初のNULLでない値

COALESCEの戻り値
ZZZ

が返ってきます。

ISNULLとの違い

SQLServerで使用できるISNULL関数のチェックする値（引数）が複数バージョンかと思いきや注意が必要で、ISNULL関数とCOALESCE関数では戻り値の型が異なります。

ISNULL関数の説明文には

構文
ISNULL( check_expression , replacement_value )
戻り値
check_expression と同じ型を返します。

とあり、COALESCE関数の説明文には

構文
COALESCE( expression [ ,…n ] )
戻り値
expression のデータ型のうち、最も優先順位が高いものを返します。

とあります。

つまり

SELECT ISNULL( CAST( 'A' AS VARCHAR), 0 )

は「’A’」という結果をVARCHAR型で受け取れますが、

SELECT COALESCE( CAST( 'A' AS VARCHAR), 0 )

は

メッセージ 245、レベル 16、状態 1、行 1
varchar の値 'A' をデータ型 int に変換できませんでした。

となり実行時エラーになります。
これはデータ型の優先順位が「VARCHAR < INT」のため 'A'をデータ型の優先順位が高いINTに変換しようとしてエラーになった結果です。上記のSQLは [sql] SELECT COALESCE( CAST( 'A' AS VARCHAR), '0' ) [/sql] と修正すれば「'A'」という結果を取得できます。