Thursday, February 23, 2017

5 Differences between COALESCE and ISNULL in SQL Server

What is the difference between COALESCE and ISNULL is one of the frequently asked Microsoft SQL Server interview question. Recently, one of my reader asked the same question to me, he got confused between these two because both are used to replace NULL values to default values in SQL Server. I'll try to answer this question here with little bit of insight about when to use ISNULL and COALESCE in SQL Server. Even though both ISNULL and COALESCE is used to provide default values for NULLs there are some key differences between them e.g. ISNULL() is a T-SQL or Microsoft SQL Server specific function or operator, and datatype and length of result depends upon parameter, but COALESCE is a SQL ANSI standard, it can accept more than one parameter and unlike ISNULL, the result doesn't directly depends upon parameter, it is always the type and length of the value returned.

This is an extremely important topic for anyone using SQL Server e.g. programmers who are working on application using Microsoft SQL Servers or DBA, who is writing stored procedures, functions and other database objects to deal with null values effectively, mainly while generating reports.

I always ask this question to anyone who claim to know about SQL Server and have used it in past. It is one of those questions, which you can use to filter the candidates who really have used SQL Server and who just claims to.

Difference between COALESCE and ISNULL in SQL Server

Those were some key differences between the collesce() and isnull() operator. Let's examine those in little bit more detail now to understand them better.

1) Avaibility
The COALESCE function is defined by the ANSI SQL standard and supported in all major databases e.g. MySQL, Oracle, PostgreSQL, DB2 etc but ISNULL() is a T-SQL (Transact SQL) function and only work with Microsoft products e.g. Microsoft SQL Server 2004, 2008, 2012, and 2014. When you use coalesce for replacing nulls with default values, your queries become more portable i.e. you can run them on other database hence It's recommended to use COALESCE over ISNULL wherever possible.

2) Number Of Parameters
The ISNULL() method takes only two parameters, it return first parameter if its not null and return the second parameter if the first parameter is null. On contrary, COALESCE can take multiple parameters, and return the NOT NULL parameter starting from first to last. Though, number of supported parameters depends upon database implementation e.g. in Microsoft SQL Server total number of supported parameter depends upon SQL Server version you are running.

3) Function over Expression
I am not fully convinced with this difference because they both look like function to me but in general ISNULL() is a function while COALESCE is expression in Microsoft SQL Server, but the word function and expression is used interchangeably in this context.

4) Evaluation
This difference is related to previous difference and somewhat answer the question that why ISNULL is considered as function while COALESCE is considered expression in SQL Server. Since ISNULL() is a function it is only evaluated once, but the input values for the COALESCE expression can be evaluated multiple times. If you want to learn more about performance in SQL Server, I also suggest reading Pro SQL Server Internals 2nd Edition by Dmitri Korotkevitch, you will find a lot of such details on this book.


5) Example :
Nothing can beat examples to understand the difference between ISNULL and COALESCE in Microsoft SQL Server. Let's see how both handles null values:

ISNULL(Name, '') will return value of column Name if its not null, otherwise it will return empty String ''.

ISNULL(Active, 'N') will return value of column Active otherwise return 'N'

COALESCE(Mobile, Phone1, Phone2) will return value of Mobile column if its NOT NULL, otherwise Phone1, if that is also NULL then value of Phone2.

Here are a couple of more examples of using ISNULL and COALESCE in SQL Server:

6) Type and length of Result
Type of the COALESCE expression is determined by the returned element, while type of the ISNULL function is determined by the first input e.g.

@j AS VARCHAR(6) = 123456

PRINT ISNULL(@i, @j);   -- outputs 1234
PRINT COALESCE (@i, @j); -- outputs 123456

because in case of ISNULL() type of return element is VARCHAR(4) and not VARHCAR(10)
hence ISNULL() returned 1234 and COALESCE() returned 123456.

Here is another example, which confirms that ISNULL() converts the replacement value (second parameter) to the type of the check expression (first parameter).

Differences between COALESCE vs ISNULL in Microsoft SQL Server

7) Using ISNULL and COALESCE in SELECT INTO Statement
One more subtle difference between COALESCE and ISNULL comes when you are using then in SELECT INTO statements.

Suppose, SELECT list of SELECT INTO statement contains the expression COALESCE(LeaveBallance, 0) as CarriedForwardLeave vs ISNULL(LeaveBalance, 0) as CarriedForwardLeave.

If the source attribute is defined as NOT NULL then both COALESCE and ISNULL() will create a NOT NULL attribute in the new table. However, if the source attribute, LeaveBalance allows NULLs, then COALESCE will create attribute allowing NULLs, whereas ISNULL() function will still create attribute with NOT NULL constraint.  See Microsoft SQL Server 2012 Internals (Developer Reference) 1st Edition by Kalen Delaney and others to learn more about this behavior in Microsoft SQL Server.

8) Speed
ISNULL is faster than COALESCE in SQL Server because its a built-in function implemented in Database engine, while COALESCE translates to CASE statements.


Here is the nice summary of all the differences between ISNULL() and COALESCE() in Microsoft SQL Server, you can go through it again to revise the concepts you have learned in this article.

Differences between COALESCE and ISNULL in SQL Server

When to use COALESCE and ISNULL function?

Now that you have learned and understood key differences between COALESCE and ISNULL in SQL Server, it's very easy to answer this question.

In general, If you want to check multiple inputs before returning default values then you should use COALESCE method as it allow multiple input, but, if you have just one column then you can use any of them.

By the way, since COALESCE is based on the ANSI SQL standard whereas ISNULL is a proprietary T-SQL function, its better to use COALESCE for database portability reason.

By the way, If you are concerned about performance and only have one value to checked upon, then consider using ISNULL() because it provide better performance since it is an in-built function into database engine and COALESCE is translated into CASE statements.

If you want  to learn more, I suggest reading Microsoft SQL Server 2012 T-SQL Fundamentals, which explains many SQL Server concepts with nice explanations and examples. Most of the concept I learned clearly is only after reading this book. It helped to clear many doubts and misconception I have about certain features of SQL Server.

5 Differences between ISNULL and COALESCE in SQL

That's all in difference between ISNULL and COALESCE in Microsoft SQL Server. It's one of the frequently asked SQL Server interview questions, hence you must know and understand the concept clearly to answer this and all follow-up question. The topic can be really tricky for casual SQL developers who claim to be working or using Microsoft SQL server for a couple of years but doesn't know much about ISNULL and COALESCE except that they can substitute null values.

Further Learning
Introduction to SQL
The Complete SQL Bootcamp
SQL for Newbs: Data Analysis for Beginners

Other SQL Server articles and tutorials you may like
  • Difference between rank(), row_number(), and dense_rank() in SQL? (answer)
  • How to remove duplicate rows from a table in SQL? (solution)
  • How to split String in SQL Server 2008? (answer)
  • How to join more than two tables in one SQL query? (solution)
  • 5 tips while migrating from Oracle to SQL Server? (tips)
  • How to find the second highest salary of an employee in SQL Server? (query)
  • What is the difference between WHERE and HAVING clause in SQL Server? (answer)
  • How to find duplicate records from a table? (solution)
  • 5 Web sites to learn SQL online for FREE? (resource)
  • How to find all customers who have never ordered? (solution)
  • What is the difference between close and deallocate a cursor? (answer)
  • How to create an Identity column in SQL Server? (example)
  • How many characters are allowed in VARCHAR(2) columns? (answer)

Thanks for reading this article, if you like this article and able to understand difference between COALESCE and ISNULL function then please share with your friends and colleagues. If you have any questions, suggestions or feedback, please drop a comment. 

1 comment :

Amit said...

A small addition, The COALESCE() function doesn't limit the number of arguments, but they must all be of the same data type.

Post a Comment