Using Isnull VS Using Coalesce for Checking a Specific Condition

Using ISNULL vs using COALESCE for checking a specific condition?

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.

SQL - Difference between COALESCE and ISNULL?

Comparing COALESCE and ISNULL

The ISNULL function and the COALESCE expression have a similar purpose but can behave differently.

  1. Because ISNULL is a function, it is evaluated only once. As described above,
    the input values for the COALESCE expression can be evaluated multiple
    times.
  2. 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.
  3. The NULLability of the result expression is different for ISNULL and COALESCE. The
    ISNULL return value is always considered NOT NULLable (assuming the return value is a
    non-nullable one) whereas COALESCE with non-null parameters is
    considered to be NULL. So the expressions ISNULL(NULL, 1) and
    COALESCE(NULL, 1) although equivalent have different nullability
    values. This makes a difference if you are using these expressions in
    computed columns, creating key constraints or making the return value
    of a scalar UDF deterministic so that it can be indexed as shown in
    the following example.
> USE tempdb; 
> GO

> -- This statement fails because the PRIMARY KEY cannot accept NULL values
> -- and the nullability of the COALESCE expression for col2
> -- evaluates to NULL.

> CREATE TABLE #Demo ( col1 integer NULL, col2 AS COALESCE(col1, 0) PRIMARY KEY, col3 AS ISNULL(col1, 0) );
>
> -- This statement succeeds because the nullability of the
> -- ISNULL function evaluates AS NOT NULL.
>
> CREATE TABLE #Demo ( col1 integer NULL, col2 AS COALESCE(col1, 0),
> col3 AS ISNULL(col1, 0) PRIMARY KEY );

Validations for ISNULL and
COALESCE are also different. For example, a NULL value for ISNULL is
converted to int whereas for COALESCE, you must provide a data type.
ISNULL takes only 2 parameters whereas COALESCE takes a variable
number of parameters.

Source: BOL

SQL Server - NULL vs blank in IF condition - ISNULL vs COALESCE

Since your variable is null, you can't concat it with the string in your if or else clause.
You must remove it...

DECLARE @V_MY_VAR VARCHAR(50) = NULL;
IF ISNULL(@V_MY_VAR,'X') = 'HELLO'
BEGIN
PRINT 'INSIDE IF - ';
END;
ELSE
BEGIN
PRINT 'INSIDE ELSE - ';
END;

...or replace it by a non null value, as example using your propose ISNULL.

DECLARE @V_MY_VAR VARCHAR(50) = NULL;
IF ISNULL(@V_MY_VAR,'X') = 'HELLO'
BEGIN
PRINT 'INSIDE IF - '+ ISNULL(@V_MY_VAR,1);
END;
ELSE
BEGIN
PRINT 'INSIDE ELSE - '+ ISNULL(@V_MY_VAR,2);
END;

You can test this here: db<>fiddle

What is the difference between IFNULL and COALESCE in MySQL?

The main difference between the two is that IFNULL function takes two arguments and returns the first one if it's not NULL or the second if the first one is NULL.

COALESCE function can take two or more parameters and returns the first non-NULL parameter, or NULL if all parameters are null, for example:

SELECT IFNULL('some value', 'some other value');
-> returns 'some value'

SELECT IFNULL(NULL,'some other value');
-> returns 'some other value'

SELECT COALESCE(NULL, 'some other value');
-> returns 'some other value' - equivalent of the IFNULL function

SELECT COALESCE(NULL, 'some value', 'some other value');
-> returns 'some value'

SELECT COALESCE(NULL, NULL, NULL, NULL, 'first non-null value');
-> returns 'first non-null value'

UPDATE: MSSQL does stricter type and parameter checking. Further, it doesn't have IFNULL function but instead ISNULL function, which needs to know the types of the arguments. Therefore:

SELECT ISNULL(NULL, NULL);
-> results in an error

SELECT ISNULL(NULL, CAST(NULL as VARCHAR));
-> returns NULL

Also COALESCE function in MSSQL requires at least one parameter to be non-null, therefore:

SELECT COALESCE(NULL, NULL, NULL, NULL, NULL);
-> results in an error

SELECT COALESCE(NULL, NULL, NULL, NULL, 'first non-null value');
-> returns 'first non-null value'

Should i use IIF and ISNULL to select a default-value?

Specify "best". Since IIF works only in SQL-Server i'd use CASE which is ANSI SQL standard and works in every(?) rdbms:

SELECT  CASE WHEN ISNULL(@value,0) = 0 THEN  @defaultValue ELSE @value END

Actually IIF is even translated to CASE:

IIF is a shorthand way for writing a CASE expression ...
The fact that IIF is translated into CASE also has an impact on other
aspects of the behavior of this function....

But the same is true for ISNULL which is also a SQL-Server function and could be replaced by COALECSE.

By the way, if you use ISNULL or COALESCE in a WHERE-clause, it prevents the query optimizer from using an index. So then you should prefer:

SELECT  ...
FROM dbo.TableName
WHERE @value IS NOT NULL AND @value <> @value

However, i prefer ISNULL over COALESCE since the latter has an issue if it contains a sub-query. It is executed twice whereas ISNULL executes it once. Actually COALESCE is also translated into CASE. You can read about that issue here.

ISNULL/COALESCE for multiple fields

Unfortunately, I can't think of a neat way to do this without being quite repetitive.

Assuming the OL aliases should have been CA:

SELECT OH.OrderNo, Item,
CASE WHEN OL.ShippingAddress IS NULL THEN OH.Line1 ELSE CA.Line1 END,
CASE WHEN OL.ShippingAddress IS NULL THEN OH.Line2 ELSE CA.Line2 END,
CASE WHEN OL.ShippingAddress IS NULL THEN OH.City ELSE CA.City END,
CASE WHEN OL.ShippingAddress IS NULL THEN OH.State ELSE CA.State END,
CASE WHEN OL.ShippingAddress IS NULL THEN OH.Zip ELSE CA.Zip END
FROM OrderHeader OH
JOIN OrderLine OL
ON OH.OrderNo = OL.OrderNo
LEFT JOIN CustomerAddress CA
ON OL.CustNum = CA.CustNum
AND OL.ShippingAddress = CA.ShippingAddress

SQL Where clause Coalese vs ISNULL VS Dynamic

The correct answer is the 'dynamic' option. It's good you left parameters in because it protects against SQL Injection (at this layer anyway).

The reason 'dynamic' is the best is because it will create a query plan that is best for the given query. With your example you might get up to 3 plans for this query, depending on which parameters are > 0, but each plan generated one will be optimized for that scenario (they will leave out unnecessary parameter comparisons).

The other two styles will generate one plan (each), and it will only be optimized for the parameters you used AT THAT TIME ONLY. Each subsequent execution will use the old plan and might be cached using the parameter you are not calling with.

'Dynamic' is not as clean-code as the other two options, but for performance, it will give you the optimal query plan each time.

Why IsNull is twice slow as coalesce (same query)?

I wonder if you'd see an improvement by splitting the cases out explicitly:

...
AND ((t1.vchCol1 = t2.vchCol1) OR (t1.vchCol1 IS NULL AND t2.vchCol1 IS NULL))
AND ((t1.vchCol2 = t2.vchCol2) OR (t1.vchCol2 IS NULL AND t2.vchCol2 IS NULL))
...


Related Topics



Leave a reply



Submit