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 )
asCASE 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
toISNULL
, 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.
- 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.- 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.- 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
How to Create Multiple One to One's
Generate_Series() Equivalent in MySQL
Illegal Mix of Collations MySQL Error
Difference Between Two Dates in MySQL
What Is the Simplest SQL Query to Find the Second Largest Value
What Is Self Join and When Would You Use It
How to Query a Value in SQL Server Xml Column
How to Return the Column Names of a Table
SQL - How to Store and Navigate Hierarchies
How to List the Primary Key of a SQL Server Table
Convert Varchar into Datetime in SQL Server
Insert into Multiple Tables in One Query
How to Split a Single Column Values to Multiple Column Values
Can a Foreign Key Be Null And/Or Duplicate
SQL Server: Howto Get Foreign Key Reference from Information_Schema
Normalize Array Subscripts So They Start With 1
SQL Server: Extract Table Meta-Data (Description, Fields and Their Data Types)