ISNULL functions are used to return the first non-null expression among the input expression. Though these two functions look similar, there are certain differences. Let’s dive in to see the differences.
COALESCE and ISNULL
To understand the basic functioning of
ISNULL, consider the following basic example:
In this case, both the functions return the same output of
1759. One advantage of
COALESCE is that it supports multiple inputs.
Main differences include:
COALESCE is ANSI Standard whereas,
ISNULL is SQL Server Specific
COALESCE can accept multiple parameters whereas,
ISNULL can accept only two parameters
Data Type Considerations
The main difference between
ISNULL is their difference in handling various data types.
The data type of a
COALESCE expression is the data type of the input with the highest data type precedence. If all inputs are un-typed
NULLs, then an error will be returned.
The data type of an
ISNULL expression is the data type of the first input. If the first input is an un-typed
NULL literal, the data type of the result is the type of the second input. If both inputs are the un-typed literals, then type of the output is
Now, consider the example:
Notice that with
COALESCE, regardless of which input is specified first, the type of the output is
VARCHAR (10) - the one with higher precedence. With
ISNULL, the type of the output depends on the first input. If the first input is
VARCHAR (3), then the result will also be
Now consider the second example:
INT data type has precedence over
VARCHAR data type, SQL Server tries to convert the value ‘
abc’ to an
INT which results in error.
COALESCE (V1, V2) is internally translated by SQL Server as:
CASE WHEN V1 IS NOT NULL THEN V1 ELSE V2 END
For testing the performance of
ISNULL, I am creating a new table called
T1 and populating with sample data as below:
Consider the following query and include the actual execution plan:
It returns a sum of
124750 and returns an execution plan like this:
Though the table
T1 is being referred only once, due to the
COALESCE function, the table is scanned twice.
Now, rewrite the query as follows:
See the execution plan.
Note that this differentiation exists only in sub queries, while in normal queries both behave similarly.
Both the statements behave similarly with no performance differentiation.
In summary, please understand the following:
ISNULL are the two functions that will return a NON-
NULL value instead of a
- The data type of the output returned by
COALESCE will be the data type with highest precedence, whereas data type of the
ISNULL output will be the data type of the first input.
COALESCE is ANSI-SQL Standard and can accept multiple parameters
- As far as the performance of the query is concerned,
ISNULL is the preferable choice in subqueries
- I am going to write my second article on the same which will give you an insight into the impact of
ISNULL in transactions.