Expressions:
NULLIF:
Returns a null value if
the two specified expressions are equal.
NULLIF (expression ,
expression )
Expression
Is
any valid scalar expression.
Applies
To
The NULLIF function can be used in the
following versions of SQL Server (Transact-SQL):
SQL Server 2014, SQL Server 2012, SQL Server
2008 R2, SQL Server 2008, SQL Server 2005
Returns the same type as the first expression.
NULLIF
returns the first expression if the two expressions are not
equal. If the expressions are equal, NULLIF returns a null value of the type of
the firstexpression.
NULLIF is equivalent to a searched CASE expression in which the
two expressions are equal and the resulting expression is NULL.
We
recommend that you not use time-dependent functions, such as RAND (), within a
NULLIF function. This could cause the function to be evaluted twice and to
return different results from the two invocations.
Examples:
SELECT
NULLIF ('TechOnTheNet.com', 'TechOnTheNet.com');
Result: NULL (returns NULL because values
are the same)
SELECT
NULLIF('CheckYourMath.com', 'TechOnTheNet.com');
Result: 'CheckYourMath.com' (returns first value because values are
different)
SELECT
NULLIF(12, 12);
Result: NULL (returns NULL because values
are the same)
SELECT
NULLIF(12, 45);
Result: 12 (returns first value because
values are different)
SELECT
NULLIF('2014-05-01', '2014-05-01');
Result: NULL (returns NULL because values
are the same)
SELECT
NULLIF('2014-05-01', '2014-04-30');
Result: '2014-05-01' (returns first value because values
are different)
COALESCE:
Evaluates the arguments in order and returns the
current value of the first expression that initially does not evaluate to NULL.
Syntax
COALESCE (
expression [ ,...n ] )
Arguments
Expression
Is an expression of any type.
Return
Types
Returns the data type of expression with the highest
data type precedence. If all expressions are nonnullable, the result is typed
as nonnullable.
Remarks
If all arguments are
NULL, COALESCE returns NULL. At least one of the null values must be a typed NULL.
Comparing COALESCE and CASE:
The
COALESCE expression is a syntactic shortcut for the CASE expression. That is,
the code COALESCE(expression1,...n) is rewritten by the query optimizer as the
following CASE expression:
CASE
WHEN (expression1 IS NOT NULL) THEN
expression1
WHEN (expression2 IS NOT NULL) THEN
expression2
...
ELSE expressionN
END
This
means that the input values (expression1, expression2, expressionN, etc.) will
be evaluated multiple times. Also, in compliance with the SQL standard, a value
expression that contains a subquery is considered non-deterministic and the
subquery is evaluated twice. In either case, different results can be returned
between the first evaluation and subsequent evaluations.
For
example, when the code COALESCE((subquery), 1) is executed, the subquery is
evaluated twice. As a result, you can get different results depending on the
isolation level of the query. For example, the code can return NULL under the
READ COMMITTED isolation level in a multi-user environment. To ensure stable
results are returned, use the SNAPSHOT ISOLATION isolation level, or replace
COALESE with the ISNULL function. Alternatively, you can rewrite the query to
push the subquery into a subselect as shown in the following example.
SELECT CASE WHEN x IS NOT NULL THEN x
ELSE 1 END
from
(
SELECT (SELECT Nullable FROM Demo
WHERE SomeCol = 1) AS x
) AS T;
COALESCE vs. ISNULL
The COALESCE and ISNULL T-SQL functions are used to return
the first nonnull expression among the input arguments. SQL Server
practitioners often wonder what the difference is between the two functions.
Several differences exist; some are straightforward and are common knowledge,
whereas others are less straightforward and are less well-known.
According
to SQL Server Books Online, COALESCE "returns the first nonnull expression
among its arguments," and ISNULL "replaces NULL with the specified
replacement value." As a simple example, the following code demonstrates
using the two functions:
SET NOCOUNT
ON;
--USE TSQL2012; -- this database is used in later
examples
DECLARE
@x AS INT = NULL,
@y AS INT = 1759,
@z AS INT = 42;
SELECT COALESCE(@x, @y, @z);
SELECT ISNULL(@x, @y);
When you
run this code, both functions return the integer 1759.
One
apparent 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. These differences between the two functions
are fairly straightforward.
Data
Type of Expression
COALESCE and ISNULL differ in how they
determine the data type of the resulting expression.
The data
type of a COALESCE expression is the data type of the input
argument with the highest data type precedence. If all inputs are the untyped
NULL literal, you get an error.
The data type of an ISNULL expression is the
data type of the first input. If the first input is an untyped NULL literal,
the data type of the result is the type of the second input. If both inputs are
the untyped literal, the type of the output is INT.
As an example, consider the following code and
its output, which Table 1 shows.
DECLARE
@x AS VARCHAR(3) = NULL,
@y AS VARCHAR(10) = '1234567890';
SELECT
COALESCE(@x, @y) AS COALESCExy , COALESCE(@y, @x) AS COALESCEyx,
ISNULL(@x, @y) AS ISNULLxy , ISNULL(@y, @x) AS ISNULLyx;
COALESCExy COALESCEyx ISNULLxy ISNULLyx
---------- ---------- -------- ---------- ------------
1234567890 1234567890 123 1234567890
Notice that with COALESCE, regardless of which
input is specified first, the type of the output is VARCHAR (10)—the one with
the higher precedence. However, with ISNULL, the type of the output is
determined by the first input. So when the first input is of a VARCHAR (3) data
type (the expression aliased as ISNULLxy), the output is VARCHAR (3). As a
result, the returned value that originated in the input @y is truncated after
three characters.
In the
last example, all inputs are character strings, just of
different lengths. What if the inputs are of different data type families? The
same rules still apply. For example, the following COALESCE expression accepts
a character string as the first input and an integer as a second input:
SELECT COALESCE ('abc', 1);
An integer has a higher data type precedence
than a character string; therefore, SQL Server tries to convert the value 'abc'
to an integer and naturally fails. This code generates the following conversion
error:
Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value
'abc' to data type int.
Try a similar expression, using ISNULL instead:
SELECT
ISNULL ('abc', 1);
Now the returned type is based on the first
input, and therefore SQL Server doesn't try to convert the character string. So
you get the output 'abc'.
As for
untyped NULL literal inputs, as I
mentioned, if all inputs to COALESCE are the untyped NULL literal, you get an
error. To demonstrate this, run the following code:
SELECT COALESCE (NULL, NULL);
You get the following error:
Msg 4127, Level 16, State 1, Line 1
At least one of the arguments to COALESCE must be an
expression that is not the NULL constant.
As long as there's a typed NULL in the input,
the code succeeds:
SELECT COALESCE(CAST(NULL AS INT), NULL);
You get a NULL typed as an integer as the
output.
The ISNULL function does allow both inputs to
be untyped NULLs, in which case it returns a NULL typed as an integer as the
output. To demonstrate this, run the following code:
SELECT ISNULL(NULL, NULL);
You get a NULL typed asan integer as the
output. To prove this, run the following code:
SELECT ISNULL(NULL, NULL) AS col1
INTO dbo.T1;
SELECT TYPE_NAME(user_type_id)
FROM sys.columns
WHERE object_id = OBJECT_ID('dbo.T1')
AND name = N'col1';
You get 'int' as output.
When you're done, run the following code for
cleanup:
DROP TABLE dbo.T1;
Nullability
of Expression
If you need to store the result of a COALESCE
or ISNULL expression in a table, you probably care whether the resulting column
is nullable or not. The way nullability of the expression is determined is
different for the two functions. To demonstrate the difference, I'll use a
table called T0, which the following code creates and populates:
SELECT CAST(NULL AS INT) AS col1 INTO dbo.T0;
With COALESCE, the resulting column is defined
as NOT NULL only if all expressions are nonnullable and NULL otherwise. With
ISNULL, the resulting column is defined as NOT NULL if any expression is
nonnullable and NULL if both are nullable. As an example, run the following code:
SELECT
COALESCE(1, 2) AS COALESCE_1_2,
COALESCE(col1, 0) AS COALESCE_col1_0,
ISNULL(col1, 0) AS ISNULL_col1_0
INTO dbo.T1
FROM dbo.T0;
COALESCE(1, 2) AS COALESCE_1_2,
COALESCE(col1, 0) AS COALESCE_col1_0,
ISNULL(col1, 0) AS ISNULL_col1_0
INTO dbo.T1
FROM dbo.T0;
Then, check the nullability of the resulting
columns by running the following query:
SELECT
COLUMNPROPERTY(OBJECT_ID('dbo.T1'), 'COALESCE_1_2',
'AllowsNull') AS COALESCE_1_2,
COLUMNPROPERTY(OBJECT_ID('dbo.T1'), 'COALESCE_col1_0',
'AllowsNull') AS COALESCE_col1_0,
COLUMNPROPERTY(OBJECT_ID('dbo.T1'), 'ISNULL_col1_0',
'AllowsNull') AS ISNULL_col1_0;
COLUMNPROPERTY(OBJECT_ID('dbo.T1'), 'COALESCE_1_2',
'AllowsNull') AS COALESCE_1_2,
COLUMNPROPERTY(OBJECT_ID('dbo.T1'), 'COALESCE_col1_0',
'AllowsNull') AS COALESCE_col1_0,
COLUMNPROPERTY(OBJECT_ID('dbo.T1'), 'ISNULL_col1_0',
'AllowsNull') AS ISNULL_col1_0;
You get the output shown in Table 2.
COALESCE_1_2 COALESCE_col1_0
ISNULL_col1_0
------------ --------------- -------------
0 1 0
------------ --------------- -------------
0 1 0
The expression COALESCE(1, 2) resulted in a
nonnullable column because both inputs were nonnullable. The expression
COALESCE(col1, 0) resulted in a nullable column because one of the expressions
was nullable (col1). The expression ISNULL(col1, 0) resulted in a nonnullable
column because one of the expressions was nonnullable (0).
When you're done, run the following code for
cleanup:
DROP TABLE dbo.T0, dbo.T1;
Used with Subqueries
The ISNULL
function has an important advantage over COALESCE in that internally it doesn't
evaluate an input expression more than once. In accordance with standard SQL,
COALESCE(v1, v2) is simply internally translated to CASE WHEN v1 IS NOT NULL
THEN v1 ELSE v2 END. As a result, SQL Server might evaluate the expression v1
more than once, which can lead to all kinds of surprising results. This section
and the next section describe a couple of interesting examples that are
affected by this behavior.
Suppose
that you need to write an expression that returns the result of a subquery when
it's not NULL and returns zero otherwise. Before trying different solutions,
run the following code in order to request I/O statistics for queries in your
session:
SET STATISTICS IO ON;
Run the
following query (call it Query 1):
SELECT SUM(qty) FROM Sales.OrderDetails;
Observe
the plan for Query 1 in Figure 1 (the first of the three plans in the figure).
The
clustered index of the OrderDetails table is scanned once, resulting in a scan
count of 1 and a logical reads count of 11. Next, try using COALESCE to
implement the task at hand by running the following query (call it Query 2):
SELECT COALESCE( (SELECT SUM(qty) FROM Sales.OrderDetails), 0 );
Examine
the plan for Query 2 in Figure 1 and observe that the clustered index of the
OrderDetails table is scanned twice. This results in a scan count of 2 and a
logical reads count of 22. This is a direct result of the fact that COALESCE
was internally translated to a CASE expression that refers to the subquery
twice—once to check if its result is NULL and again to return the result
because it isn't NULL.
Implement
a solution using ISNULL by running the following query (call it Query 3):
SELECT ISNULL( (SELECT SUM(qty) FROM Sales.OrderDetails), 0 );
Examine
the plan for Query 3 in Figure 1 and observe that the clustered index of the
OrderDetails table is scanned only once. This results in a scan count of 1 and
a logical reads count of 11.
In this
particular case, you could first store the result of the subquery in a variable
and use the variable in the COALESCE expression, but that's not always
possible. For example, consider a case where the subquery is a correlated one
appearing in some outer query against another table. So from this perspective,
ISNULL is preferred to COALESCE.
When
you're done, run the following code to turn off reporting I/O statistics in the
session:
SET STATISTICS IO OFF;
Atomicity vs. Isolation
This
section covers another example for the implications of SQL Server's conversion
of a COALESCE expression to a CASE expression. Before I present the example,
try to think of a scenario in which the expression COALESCE( <subquery>,
0 ) returns a NULL.
Now let's
look at the example. Open two connections to SQL Server (call them connection 1
and connection 2). Run the following code in connection 1:
CREATE TABLE dbo.T1(col1 INT);
INSERT INTO dbo.T1 VALUES(42);
BEGIN TRAN
INSERT INTO dbo.T1 VALUES(1759);
Then, run
the following code in connection 2:
SELECT COALESCE( (SELECT SUM(col1) FROM dbo.T1), 0 );
Back in
connection 1, run the following code:
DELETE FROM dbo.T1;
COMMIT TRAN
DROP TABLE dbo.T1;
Connection
2 returns a NULL. Can you explain how this could happen?
In order
to explain what happened, you need to examine the execution plan for the query
in connection 2 shown in Figure 2.
The
COALESCE expression in the query in connection 2 was internally translated to
the following CASE expression:
CASE
WHEN (SELECT SUM(col1) FROM dbo.T1) IS NOT NULL
THEN (SELECT SUM(col1) FROM dbo.T1)
ELSE 0
END
The upper
branch of the query plan calculates the result of the query in the WHEN clause
of the CASE expression. It reads the row with the value 42 and then blocks,
because the row with the value 1759 is exclusively locked. Then, connection 1
deletes all rows from the table and commits, allowing the query to complete.
The resulting sum 42 is assigned to the variable Expr1004. Next, the bottom
branch of the query plan calculates the result of the query in the THEN clause
of the CASE expression. The query returns a NULL because there are no rows in
the table at this point, and the NULL result is assigned to the variable
Expr1010. Now the CASE expression looks like this:
CASE WHEN Expr1004 IS NOT NULL THEN Expr1010 ELSE 0 END
Or, with
constants:
CASE WHEN Expr1004 IS NOT NULL THEN Expr1010 ELSE 0 END
You might
think that the atomicity of the SELECT transaction is broken here, but it
isn't—you still get an all-or-nothing behavior. Under the default isolation
level read committed, there's no guarantee that the code will interact with a
frozen state of the data. To achieve such behavior, you need to request to work
with the serializable isolation level. For example, repeat the same test that
you just ran, but this time request to use the serializable isolation level in
the query in connection 2, like so:
SELECT COALESCE( (SELECT SUM(col1) FROM dbo.T1 WITH
(SERIALIZABLE)), 0 );
This time
you get the output 0. Examine the plan for the query in connection 2 shown in
Figure 3.
This time
the query in the WHEN clause blocks before any rows are scanned waiting for the
delete transaction to complete. Consequently, both branches of the plan find
zero rows in the table, and result in a NULL. Therefore, the CASE expression
looks like this:
CASE WHEN NULL IS NOT NULL THEN NULL ELSE 0 END
Try again,
but this time using ISNULL instead of COALESCE:
SELECT ISNULL( (SELECT SUM(col1) FROM dbo.T1), 0 );
This time
you get the output 42. By examining the plan in Figure 4, you can understand
why.
The query
was executed only once. It blocked after reading the row with the value 42.
After the delete transaction committed, there were no more rows to read; hence
the sum was computed as 42. Because that sum wasn't NULL, the value 42 was
returned.
Significant Differences
On the
surface, it might seem that there are only a couple of minor differences
between the COALESCE and ISNULL functions. In practice, there are quite a few
differences—some of which are significant. This article describes not only the
minor and straightforward differences but also the more complex and significant
ones. Some of the differences make the COALESCE function preferable, whereas
others make ISNULL preferable. Armed with this knowledge, you can make an
educated decision about which function to use based on your priorities.
The COALESCE expression is a syntactic shortcut for the CASE expression. That is, the code
COALESCE(expression1,...n) is rewritten by the query optimizer as the following CASE expression:CASEWHEN (expression1 IS NOT NULL) THEN expression1WHEN (expression2 IS NOT NULL) THEN expression2...ELSE expressionNEND
This means that the input values (expression1, expression2, expressionN, etc.) will be evaluated multiple times. Also, in compliance with the SQL standard, a value expression that contains a subquery is considered non-deterministic and the subquery is evaluated twice. In either case, different results can be returned between the first evaluation and subsequent evaluations.
For example, when the code
COALESCE((subquery), 1) is executed, the subquery is evaluated twice. As a result, you can get different results depending on the isolation level of the query. For example, the code can return NULL under the READ COMMITTED isolation level in a multi-user environment. To ensure stable results are returned, use the SNAPSHOT ISOLATION isolation level, or replace COALESE with the ISNULL function. Alternatively, you can rewrite the query to push the subquery into a subselect as shown in the following example.SELECT CASE WHEN x IS NOT NULL THEN x ELSE 1 END from ( SELECT (SELECT Nullable FROM Demo WHERE SomeCol = 1) AS x ) AS T;
No comments:
Post a Comment