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 questions. Recently, one of my readers 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 a 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 like ISNULL() is a T-SQL or Microsoft SQL Server-specific function or operator, and datatype and length of the result depends upon a parameter, but COALESCE is a SQL ANSI standard, it can accept more than one parameter and unlike ISNULL, the result doesn't directly depend upon a 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 an 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 claims to know about SQL Server and has used it in the past. It is one of those questions, which you can use to filter the candidates who really have used SQL Server and who just claim to.

By the way, if you are new to Microsoft SQL Server and T-SQL then I also suggest you join a comprehensive course to learn SQL Server fundamentals and how to work with T-SQL. If you need a recommendation then I suggest you go through the Microsoft SQL for Beginners online course by Brewster Knowlton on Udemy. It''s a great course to start with T-SQL and SQL queries in SQL Server.





Difference between COALESCE and ISNULL in SQL Server

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



1) Availability


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 databases hence It's recommended to use COALESCE over ISNULL wherever possible.



2) Number Of Parameters

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


3) Function over Expression

I am not fully convinced with this difference because they both look like a 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 the 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.

COALESCE vs ISNULL in SQL Server



5) Example :

Nothing can beat examples to understand the difference between ISNULL and COALESCE in Microsoft SQL Server. Let's see how both handle 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.

DECLARE
@i AS VARCHAR(4) = NULL,
@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 VARCHAR(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, a 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 the ISNULL() function will still create an 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 of its a built-in function implemented in the Database engine, while COALESCE translates to CASE statements.


Summary

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 the COALESCE method as it allows multiple inputs, 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 provides 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 misconceptions I have about certain features of SQL Server.

5 Differences between ISNULL and COALESCE in SQL



That's all in the 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 questions. 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 don'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