読者です 読者をやめる 読者になる 読者になる

COALESCE と ISNULL

db sql sqlserver

SQLServer(MSSQL)には ISNULL という関数があります。
本記事では以下の記事を参考に COALESCE と ISNULL の違いをまとめます。
COALESCE vs. ISNULL T-SQL Functions | T-SQL content from SQL Server Pro

また、本記事では通常の開発で意識すべき内容だと判断したもののみ記載します。
参考にさせて頂いた記事には、こちらでは触れていない内容もございますので、興味がある方はそちらの記事をご覧頂ければと思います。

サマリ

COALESCE ISNULL
定義 ISOで定義された式 MSが定義した関数
引数の数 any 2
データ型 優先順位が高い型 1つ目の値の型
サブクエリ 下記参照
型指定されていないNULL 下記参照

定義

COALESCE はISOで定義された式ですが、ISNULL はSQLServer(MSSQL)独自の関数です。

引数の数

COALESCE は複数の値を渡せますが、ISNULL は2つしか渡せません。

データ型

COALESCE は与えられた値の中で優先順位が高いデータ型になります。
ISNULL は1つ目の値のデータ型になります。

データ型の優先順位については下記を参照下さい。
データ型の優先順位 (Transact-SQL)

サンプル

具体的には下記のようなクエリで注意が必要です。

DECLARE @x nchar(5) = '01234'
DECLARE @xNull nchar(5) = null
DECLARE @y nchar(10) = '0123456789'
DECLARE @yNull nchar(10) = null

SELECT 
   ISNULL(@xNull, @y) AS ISN_xNull_y
  ,COALESCE(@xNull, @y) AS COA_xNull_y

  ,ISNULL(@x, @yNull) AS ISN_yNull_x
  ,DATALENGTH(ISNULL(@x, @yNull)) AS ISN_yNull_x_LEN
  ,COALESCE(@x, @yNull) AS COA_yNull_x
  ,DATALENGTH(COALESCE(@x, @yNull)) AS COA_yNull_x_LEN
ISN_xNull_y COA_xNull_y ISN_yNull_x ISN_yNull_x_LEN COA_yNull_x COA_yNull_x_LEN
01234 0123456789 01234 10 01234 20

※LEN関数では末尾の空白を除いた文字数が取得されるため、DATALENGTH関数を使用しています。
LEN (Transact-SQL)
DATALENGTH (Transact-SQL)

ISN_xNull_y の結果として 0123456789 を期待したかもしれませんが、1つ目の値の型( nchar(5) )になるため、結果は 01234 になります。

COA_yNull_x_LEN の結果として 10 を期待したかもしれませんが、優先順位が高い型( nchar(10) )になるため、結果は 20 になります。
つまり、 COA_yNull_x には空白が含まれているということです。

COALESCE と ISNULL のどちらを使うにせよ、値の型が異なる場合は注意が必要です。

サブクエリ

COALESCE は case-when のシンタックスシュガーでしかないため、
下記のように COALESCE 内でサブクエリを指定すると、
サブクエリが2回実行されパフォーマンスが悪化します。

SELECT COALESCE(NULL, (SELECT COUNT(*) FROM Products))

型指定されていないNULL

COALESCE は1つ以上型指定されたNULLを指定する必要があります。
ISNULL に型指定されていないNULLを指定すると、int型の NULL になります。

最後に

以上です。気になる点等ございましたら、コメント等頂けると幸いです。