Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Using ISNULL vs using COALESCE for checking a specific condition?

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?

like image 545
JBone Avatar asked Sep 13 '11 21:09

JBone


People also ask

Is coalesce better than Isnull?

advantage that COALESCE has over ISNULL is that it supports more than two inputs, whereas ISNULL supports only two. Another advantage of COALESCE is that it's a standard function (namely, defined by the ISO/ANSI SQL standards), whereas ISNULL is T-SQL–specific.

What is the difference between Isnull () vs coalesce () function?

Comparing COALESCE and ISNULL Data type determination of the resulting expression is different. ISNULL uses the data type of the first parameter, COALESCE follows the CASE expression rules and returns the data type of value with the highest precedence.

Can you use user-defined type in Isnull () or coalesce ()?

The ISNULL and Coalesce functions are both used to replace null values with a user-defined value.

What can I use instead of coalesce?

mysql - SQL: Alternative to COALESCE() function that groups all values into one column - Stack Overflow.


1 Answers

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.

like image 169
onedaywhen Avatar answered Sep 22 '22 02:09

onedaywhen