4 Differences between ISNULL vs COALESCE in SQL Server

Even though both ISNULL() and COALESCE() function provides alternate values to NULL in SQL Server e.g. replacing NULL values with empty String, there are some key differences between them, and the key difference between them is that  COALESCE() is a standard function but ISNULL() is a SQL Server specific, which means it's not guaranteed to be supported by other database vendors like Oracle, MySQL or PostgreSQL. But, perhaps the most important difference between them is that COALESCE is more flexible and powerful than ISNULL(). With ISNULL(), you can only provide one alternate value but with COALESCE you can provide more than one e.g. if col1 IS NULL then take value from column2, if that is NULL then take the default value. Btw, that's not the only differences, there are three key differences between ISNULL() and COALESCE() which we will explore in this article.


ISNULL() vs COALESCE()

There are three major differences between these two function besides being ANSI standard or not:

1) COALESCE correctly promotes its arguments to the highest data type in the expression list, but ISNULL doesn't.

2) COALESCE is more flexible and allows you to provide multiple columns and default values but ISNULL can only work with two values.

3) In the case of ISNULL, the alternate value takes the length of the first value but this doesn't happen in the case of COALESCE. Which means the type of the COALESCE expression is determined by the returned element whereas the return type of ISNULL is determined by the first input.

4) When you use then in a SELECT INTO query then both will produce an NON-NULL value in result table if the attribute has NON NULL constraint but if it doesn't then COALESCE will create an attribute which allows NULL and ISNULL will create which doesn't allow NULLs
Now let's understand each point in little more detail.



1st Difference - COALESCE promotes its argument to the higher data type.

As I have said before that, COALESCE correctly promotes its arguments to the highest data type in the expression list, while ISNULL just looks the data type of the first argument and makes everything of that type. Let's see an SQL query to understand this point:

SELECT 19 / ISNULL(CONVERT(INT,NULL), 3.00);
Output
6

SELECT 19 / COALESCE(CONVERT(INT,NULL), 3.00)
Output
6.333333

In first SQL query, we have used ISNULL and first data type is INT but because of its NULL, it also converts 3.00 to INT and performed integer arithmetic, but COALESCE correctly promotes the 19 to FLOAT and performed floating point arithmetic.



2nd Difference - COALESCE allows multiple values

You can provide COALESCE multiple values to use in case target is NULL. For example, in the following query, we have provided four options to COALESCE

DECLARE @x VARCHAR(10)
DECLARE @y VARCHAR(10)
DECLARE @z VARCHAR(10)
DECLARE @a VARCHAR(10)
SELECT @a = 'SQL'

--This will return SQL
SELECT COALESCE(@x,@y,@z,@a)

Output
SQL

With ISNULL, you can only provide two values e.g.

SELECT ISNULL(@x,@y); --NULL
SELECT ISNULL(@x,@a); --SQL

This flexibility allows you to replace complex case statement with simple coalesce function call on SQL Server stored procedure and functions.  See Querying Microsoft SQL Server 2012  to learn more about it.




3rd Difference - Length of Result

In the case of COALESCE data type of the result value determines the type of COALESCE expression but in the case of ISNULL it's the type of the first argument. For example, see the following T-SQL Query:

DECLARE

@a AS VARCHAR(4) = NULL,
@b AS VARCHAR(10) = '1234567890';

SELECT COALESCE(@a, @b) AS [COALESCE], ISNULL(@a, @b) AS [ISNULL];

Output
COALESCE ISNULL
1234567890 1234

You can see that in the case of COALESCE() the result has type and length VARCHAR(10) but in the case of ISNULL() its the length of first value i.e. length is 4 character. Another worth noting thing is the use of square bracket e.g. [ISNULL], we do this when we use any keyword or function as literal i.e. variable name or column name.  If you want to learn more, I suggest you reading Microsoft SQL Server 2012 T-SQL Fundamentals, one of the best books to understand SQL Server.





4th Difference - When using with SELECT INTO

One more difference between COALESCE and ISNULL comes when you are using them in SELECT INTO clause. If you don't know you can create a table by copying data and schema from another table by using SELECT INTO clause.

If you are using something like:
  COALESCE(column1, 0) as new_column
vs
ISNULL(column1, 0) as new_column.

Then, both expression will produce a NOT NULL attribute in result table if the source is defined as NOT NULL, but in case source attribute allows NULLs then COALESCE will create an attribute which allows NULL and ISNULL will create which doesn't allow NULLs.

Finally, here is a nice summary of differences between coalesce and isnull function in Microsoft SQL Server.

4 Differences between ISNULL vs COALESCE in SQL Server


That's all about the difference between ISNULL and COALESCE in SQL Server. Generally, it's recommended to stick to standard features unless there is some flexibility or major performance you get by using a non-standard feature. Since ISNULL is actually more limited than COALESCE, so there is no reason to use ISNULL over COALESCE, unless you find ISNULL more readable than COALESCE, like many beginners. Btw, you must remember these key differences between ISNULL and COALESCE if you are refactoring code and replacing ISNULL with COALESCE in your SQL Script.

Other SQL Server articles you may like to explore
  • Difference between rank(), row_number(), and dense_rank() in SQL? (answer)
  • Difference between Cast, Convert, and Parse in SQL Server? (answer)
  • The right way to compare dates in SQL query? (example)
  • How to remove duplicate rows from a table in SQL? (solution)
  • How to split String in SQL Server 2008? (answer)
  • 5 Web sites to learn SQL online for FREE? (resource)
  • How to find the length of a String in SQL Server? (example)
  • How to find all customers who have never ordered? (solution)
  • How to convert the result of a SELECT command into a CSV String? (example)
  • What is the difference between close and deallocate a cursor? (answer)
  • How to create an Identity column in SQL Server? (example)
  • How to add columns into an existing table in MSSQL? (example)

Thanks for reading this article, if you like the information is given here then please share with my friends and colleagues. 

No comments:

Post a Comment