Home > Differences > ISNULL vs COALESCE

ISNULL vs COALESCE


ISNULL & COALESCE with some common features makes them equivalent, but some features makes them work and behave differently, shown below.

- Similarity
Both can be use to build/create a CSV list as shown below:

USE [AdventureWorks]
GO

DECLARE @csv VARCHAR(2000)

SELECT @csv = ISNULL(@csv + ', ', '') + FirstName
FROM Person.Contact
WHERE ContactID <= 10
ORDER BY FirstName

select @csv

set @csv=NULL
SELECT @csv = COALESCE(@csv + ', ', '') + FirstName
FROM Person.Contact
WHERE ContactID <= 10
ORDER BY FirstName

select @csv
Both will give the same output:
Carla, Catherine, Frances, Gustavo, Humberto, Jay, Kim, Margaret, Pilar, Ronald

- Difference #1
ISNULL accepts only 2 parameters. The first parameter is checked for NULL value, if it is NULL then the second parameter is returned, otherwise it returns first parameter.
COALESCE accepts two or more parameters. One can apply 2 or as many parameters, but it returns only the first non NULL parameter, example below.

DECLARE @str1 VARCHAR(10), @str2 VARCHAR(10)

-- ISNULL() takes only 2 arguments
SELECT ISNULL(@str1, 'manoj') AS 'IS_NULL' -- manoj

-- COALESCE takes multiple arguments and returns first non-NULL argument
SELECT COALESCE(@str1, @str2, 'manoj') AS 'COALESCE' -- manoj

-- ISNULL() equivalent of COALESCE, by nesting of ISNULL()
SELECT ISNULL(@str1, ISNULL(@str2, 'manoj')) AS 'IS_NULL eqv' -- manoj

- Difference #2
ISNULL does not implicitly converts the datatype if both parameters datatype are different.
On the other side COALESCE implicitly converts the parameters datatype in order of higher precedence.

-- ISNULL Does not do Implicit conversion
select ISNULL(10, getdate()) as 'IS_NULL' -- Errors out
Error Message:
Msg 257, Level 16, State 3, Line 1
Implicit conversion from data type datetime to int is not allowed.
Use the CONVERT function to run this query.
-- COALESCE Does Implicit conversion and gets converted to higher precedence datatype.
select COALESCE(10, getdate()) as 'COALESCE' -- 1900-01-11 00:00:00.000, outputs 10 but convert it to datetime [datetime > int]
select COALESCE(getdate(),10) as 'COALESCE' -- {Current date} 2010-12-23 23:36:31.110
select COALESCE(10, 'Manoj') as 'COALESCE' -- 10 [int > varchar]
select COALESCE('Manoj',10) as 'COALESCE' -- Errors out, it does an implicit conversion, but cannot change 'Manoj' to Integer.
Error Message:
Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value 'Manoj' to data type int.

- Difference #3
Similar to above point ISNULL always returns the value with datatype of first parameter.
Contrary to this, COALESCE returns the datatype value according to the precedence and datatype compatibility.

DECLARE @str VARCHAR(5)

SET @str = NULL

-- ISNULL returns truncated value after its fixed size, here 5
SELECT ISNULL(@str, 'Half Full') AS 'IS_NULL' -- Half

-- COALESCE returns full length value, returns full 12 char string
SELECT COALESCE(@str, 'Half Full') AS 'COALESCE' -- Half Full

- Difference #4
According to MS BOL, ISNULL and COALESCE though equivalent, can behave differently. An expression involving ISNULL with non-null parameters is considered to be NOT NULL, while expressions involving COALESCE with non-null parameters is considered to be NULL. Thus to index expressions involving COALESCE with non-null parameters, the computed column can be persisted using the PERSISTED column attribute.

-- ISNULL() is allowed in computed columns with Primary Key
CREATE TABLE T1 (
	col1 INT,
	col2 AS ISNULL(col1, 1) PRIMARY KEY)

-- COALESCE() is not allowed in non-persisted computed columns with Primary Key
CREATE TABLE T2 (
	col1 INT,
	col2 AS COALESCE(col1, 1) PRIMARY KEY)
Error Message:
Msg 1711, Level 16, State 1, Line 1
Cannot define PRIMARY KEY constraint on column 'col2' in table 'T2'. The computed column has to be persisted and not nullable.
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.
-- COALESCE() is only allowed as persisted computed columns with Primary Key
CREATE TABLE T2 (
	col1 INT,
	col2 AS COALESCE(col1, 1) PERSISTED PRIMARY KEY)

-- Clean up
DROP TABLE T1
DROP TABLE T2

MSDN BOL links:
ISNULL: http://msdn.microsoft.com/en-us/library/ms184325.aspx
COALESCE: http://msdn.microsoft.com/en-us/library/ms190349.aspx
http://code.msdn.microsoft.com/SQLExamples/Wiki/View.aspx?title=ISNULL_COALESCE

About these ads
  1. December 24, 2010 at 1:15 am

    More info from Kalman (SQLUSA): http://www.sqlusa.com/bestpractices2005/coalesce/

  2. November 14, 2011 at 6:45 pm

    Nice article, well written….

  3. December 14, 2011 at 1:19 pm

    Good one. simple and clear

  4. ammar
    January 18, 2012 at 8:49 am

    Very good Explaination, Simple and Clear…
    Only I didn’t get the last Difference i.e. Difference #4

    • February 22, 2012 at 4:04 am

      Hi @ammar,
      Check my comments in green above each SQL statement in diff #4.

  5. ank
    January 20, 2012 at 7:55 pm

    Brilliantly highlighted differences.

  6. joko
    February 22, 2012 at 3:34 am

    Nice..
    Really Appreciate it!

  7. alexms_2001
    March 15, 2012 at 7:12 pm

    Thanks, Manoj, the more so because MSDN’s doc on COALESCE (http://msdn.microsoft.com/en-us/library/ms190349.aspx) contains a confusing typo: “expressions involving COALESCE with non-null parameters is considered to be NULL”.
    However, your very helpful samples contain an error:

    select COALESCE(10, ‘Manoj’) as ‘result_of_COALESCE’ — 10 [int > varchar] – No! int is higher and 10 stays being an int!

    Here is the proof:

    declare @qqq int, @www nvarchar(max);
    select @qqq = COALESCE(10, ‘Manoj’) + 6; — works ok
    select @qqq;
    select @www = COALESCE(10, ‘Manoj’) + ‘abc'; — “Conversion failed when converting the varchar value ‘abc’ to data type int.”
    select @www;

    • March 17, 2012 at 10:35 am

      @alexms_2000, that’s what I’ve mentioned in the comments right [int > varchar] in line-4, means int has greater/higher precedence than varchar. I think you misunderstood the ‘>’ symbol.

      Thanks for your comments :)

      • alexms_2001
        March 17, 2012 at 8:12 pm

        Yes I did (I misinterperted the “>”)…

  8. alexms_2001
    March 15, 2012 at 7:15 pm

    Follow-up: that HTML thing for reasons unknown converts normal single quotes in the post text into something else (‘abc’ – ?), so be careful…

  9. pbora
    March 23, 2012 at 8:32 pm

    A fabulous explanation with good examples.

  10. November 10, 2013 at 2:40 pm

    You need to be a part of a contest for one of the greatest blogs online.
    I will recommend this blog!

  1. December 23, 2010 at 6:38 pm
  2. November 19, 2012 at 12:37 am
  3. November 6, 2013 at 5:34 pm
  4. June 30, 2014 at 7:58 am

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 399 other followers

%d bloggers like this: