Using ISNULL vs using COALESCE for checking a specific condition?
SqlTsqlCoalesceIsnullSql Problem Overview
I know that multiple parameters can be passed to COALESCE
, but when you want to to
check just one expression to see if it doesn't exist, do you use a default or is it a better practice to use ISNULL
instead?
Is there any performance gain between the two?
Sql Solutions
Solution 1 - Sql
This problem reported on Microsoft Connect reveals some differences between COALESCE
and ISNULL
:
> an early part of our processing rewrites COALESCE( expression1, expression2 )
as CASE WHEN expression1 IS NOT NULL THEN expression1 ELSE expression2 END
. In [this example]:
> COALESCE ( ( SELECT Nullable FROM Demo WHERE SomeCol = 1 ), 1 )
> we generate:
> SELECT CASE WHEN (SELECT Nullable FROM Demo WHERE SomeCol = 1) IS NOT NULL THEN (SELECT Nullable FROM Demo WHERE SomeCol = 1) ELSE 1 END
> Later stages of query processing don't understand that the two subqueries were originally the same expression, so they execute the subquery twice...
>
> One workaround, though I hate to suggest it, is to change COALESCE
to ISNULL
, since the latter doesn't duplicate the subquery.
Solution 2 - Sql
I think not, but COALESCE is in the SQL '92 standard and supported by more different databases. If you go for portability, don't use ISNULL.
Solution 3 - Sql
In COALESCE you can have multiple expressions, where as in ISNULL you can check only one expression
COALESCE ( expression [ ,...n ] )
ISNULL ( check_expression , replacement_value )
Solution 4 - Sql
Worth mentioning is that the type handling between the two can also make a difference (see this related answer item (2)).
Say a query tries to use a shortcut for writing null comparison:
select * from SomeTable
where IsNull(SomeNullableBitField, -1) != IsNull(SomeOtherNullableBitField, -1);
which is different than
select * from SomeTable
where coalesce(SomeNullableBitField, -1) != coalesce(SomeOtherNullableBitField, -1);
Because in the first case, the IsNull() forces the type to be a bit (so -1 is converted to true) whereas the second case will promote both to an int.
with input as
(
select convert(bit, 1) as BitOn,
convert(bit, 0) as BitOff,
convert(bit, null) as BitNull
)
select BitOn,
BitOff,
BitNull,
IsNull(BitOn, -1) IsNullBitOn, -- true
IsNull(BitOff, -1) IsNullBitOff, -- false
IsNull(BitNull, -1) IsNullBitNull, -- true, converts the -1 to bit
coalesce(BitOn, -1) CoalesceBitOn, -- 1
coalesce(BitOff, -1) CoalesceBitOff, -- 0
coalesce(BitNull, -1) CoalesceBitNull -- -1
from input;
There is a similar comment/link (@Martin Smith) on the question itself.
Solution 5 - Sql
One major thing that I don't see explicitly indicated is that ISNULL
's output type is similar to the first expression but with COALESCE
it returns the datatype of value of highest precedence.
DECLARE @X VARCHAR(3) = NULL
DECLARE @Y VARCHAR(10) = '123456789'
/* The datatype returned is similar to X, or the first expression*/
SELECT ISNULL(@X, @Y) ---> Output is '123'
/* The datatype returned is similar to Y, or to the value of highest precedence*/
SELECT COALESCE(@X, @Y) ---> Output is '123456789'
Solution 6 - Sql
The NULL
and COALESCE
are not always interchangeable. It deserves to know their differences in order to know when its better to use the one over the other:
The table above is comparison between ISNULL
and COALESCE
from Exam Ref 70-761 Querying Data with Transact-SQL
book written by Itzik Ben-Gan.
-
Number of supported parameters -
2
forISNULL
vs>2
when usingCOALESCE
-
ISNULL
is proprietary T-SQL feature andCOALESCE
is ISO/ANSI SQL standard -
The data type of the result is important. After reading notes in the table above, check the following cases:
DECLARE @x VARCHAR(3) = NULL ,@y VARCHAR(10) = '1234567890'; SELECT ISNULL(@x, @y) AS [ISNULL], COALESCE(@x, @y) AS [COALESCE];
The ISNULL
is getting the data type of the first argument as it is the not
NULL
literal. It is VARCHAR(3)
and is a result, the second argument data
is cut to match it. With COALESCE
the data type if highest precedence is
used.
DECLARE @x VARCHAR(8) = '123x5'
,@y INT = 123;
SELECT ISNULL(@x, @y) AS [ISNULL];
SELECT COALESCE(@x, @y) AS [COALESCE];
The ISNULL
is returning the data type of first argument, while in
COALESCE
we are getting error, as the INT
has highest precedence and the
conversion of the first argument value to INT
fails.
-
The nullability of the result can be important, too. For, example:
DECLARE @x VARCHAR(3) = NULL ,@y VARCHAR(3) = NULL; DROP TABLE IF EXISTS [dbo].[DataSource01]; SELECT ISNULL(10, 20) AS [C1] ,ISNULL(@x, 'text') AS [C2] ,ISNULL(@x, @y) AS [C3] INTO [dbo].[DataSource01]; DROP TABLE IF EXISTS [dbo].[DataSource02]; SELECT COALESCE(10, 20) AS [C1] ,COALESCE(@x, 'text') AS [C2] ,COALESCE(@x, @y) AS [C3] INTO [dbo].[DataSource02];
Let's check the Nullable
property of each column:
Using COALESCE
we have a NOT NULL
property of column set to Yes
, only
when all of the inputs are non null-able.
-
According to the SQL standard, the
COALESCE
expression is translated to:CASE WHEN (<subquery>) IS NOT NULL THEN (<subquery>) ELSE 0 END
> If the result of the execution of the subquery in the WHEN clause isn’t NULL, SQL Server executes it a second time in the THEN clause. > In other words, in such a case it executes it twice. Only if the > result of the execution in the WHEN clause is NULL, SQL Server doesn’t > execute the subquery again, rather returns the ELSE expression. So > when using subqueries, the ISNULL function has a performance > advantage.
Solution 7 - Sql
This explanation gives clear about coalesce vs isnull
The COALESCE function in SQL returns the first non-NULL expression among its arguments. The syntax for COALESCE is as follows:
COALESCE ("expression 1", "expressions 2", ...)
It is the same as the following CASE statement:
SELECT CASE ("column_name")
WHEN "expression 1 is not NULL" THEN "expression 1"
WHEN "expression 2 is not NULL" THEN "expression 2"
...
[ELSE "NULL"]
END
FROM "table_name";
In SQL Server, the ISNULL( ) function is used to replace NULL value with another value.
select CountryName = ISNULL("columnname", 'INDIA') from Countries
Coalesce return first non null expression where as isnull() is used to replace null value with our desired value.
COALESCE is a part of ANSI standards and are available in almost all databases.
when deciding between ISNULL v COALESCE there parameters has to be taken care off:
-
COALESCE determines the type of the output based on data type precedence where as With ISNULL, the data type is not influenced by data type precedence.
-
Consider following sql statements
DECLARE @c5 VARCHAR(5); SELECT 'COALESCE', COALESCE(@c5, 'longer name') UNION ALL SELECT 'ISNULL', ISNULL(@c5, 'longer name');
Results:
COALESCE longer name
ISNULL longe
This happens because ISNULL takes the data type of the first argument, while COALESCE inspects all of the elements and chooses the best fit (in this case, VARCHAR(11))
For more detailed explanation on deciding between COALESCE vs ISNULL check this: https://www.mssqltips.com/sqlservertip/2689/deciding-between-coalesce-and-isnull-in-sql-server/
Solution 8 - Sql
Where there is only one null condition, ISNULL
will have less overhead. The difference is probably negligible, though.
Solution 9 - Sql
In COALESCE one can use multiple expressions, It will return value which is not a null and occurs first... for example
DECLARE @Value1 INT, @Value2 INT, @Value3 INT, @Value4 INT
SELECT @Value2 = 2, @Value4 = 4
SELECT COALESCE(@Value1, @Value2, @Value3, @Value4)
SELECT COALESCE(@Value1, @Value4, @Value3, @Value2)
And in ISNULL if expression null it will return second parameter provided, and of course you can check only for one expression...
So if want check multiple expression and select first not null among them, then use coalesce otherwise go for ISNULL