Functions COALESCE and ISNULL in T-SQL – features and main differences

In the T-SQL language there are such functions as COALESCE and ISNULL, which can check the incoming parameters to the value of NULL, today we will consider the features of these functions, as well as compare them and determine what the differences between them.

Previously, we have already considered the basics of programming in T-SQL, as well as made a brief guide to this language, but in detail about the functions of COALESCE and ISNULL, we have not talked and certainly not compared them. And since beginners T-SQL programmers may have the impression that the functions COALESCE and ISNULL work exactly the same way, which is actually not the case, I suggest to talk about these functions in more detail, namely to find out what the differences between them and in what cases it is better to use a particular function.

Note! Functions TRIM, LTRIM and RTRIM in T-SQL – description, differences and examples.

COALESCE

COALESCE – function T-SQL, which returns the first expression from the list of parameters, unequal NULL.

Syntax
COALESCE (input parameters [,...n])

Features
Expressions of any data type, including subqueries, can be used as parameters. If all parameters are equal to NULL, the function will return NULL. The type of the returned value is equal to the type of the highest priority value data. For example, 5 parameters are specified and all of them have different data types, the first value different from NULL will be returned with the data type that has the highest priority among all types of values specified in the parameters.

As stated by the developers, i.e., it is specified in the official documentation, the function COALESCE is equivalent to the expression CASE, i.e., only its syntactic abbreviation. For example, let’s write a query with two expressions, one using the COALESCE function and the other using CASE, which will work the same way.

DECLARE @Var1 VARCHAR(5)
DECLARE @Var2 VARCHAR(5)
DECLARE @Var3 VARCHAR(5)

SET @Var1 = NULL
SET @Var2 = NULL
SET @Var3 = 'Var3'.

SELECT COALESCE(@Var1, @Var2, @Var3, 'All parameters empty') AS [COALESCE],
CASE WHEN @Var1 IS NOT NULL THEN @Var1
WHEN @Var2 IS NOT NULL THEN @Var2
WHEN @Var3 IS NOT NULL THEN @Var3
ELSE 'All parameters are empty'.
END AS [CASE]

ISNULL

ISNULL is a T-SQL function that replaces the NULL value of the first parameter with the specified value in the second parameter. In other words, if the first parameter is NULL, the second parameter will be returned.

Syntax
ISNULL (check_expression, replacement_value)

Where is ?

• check_expression is an expression that is checked for NULL;
• replacement_value – an expression returned if check_expression value is NULL.

Features
The data type of the replacement_value expression must be explicitly converted to the check_expression value’s data type; as a result, the ISNULL function has the return value’s type equal to the check_expression data type.

Example

DECLARE @Var1 VARCHAR(35)
SET @Var1 = NULL
SELECT ISNULL (@Var1, 'First parameter value NULL') AS [ISNULL]

Comparison of COALESCE and ISNULL – their main differences

At first glance, it may seem that these functions work the same way except that the ISNULL function has two parameters and COALESCE may have several, but there are other more important differences.

• The first is, as already noted, the ISNULL function accepts only 2 parameters, while COALESCE accepts a variable number of parameters;
• The ISNULL function returns the data type of the first parameter, so the substitute value specified in the second parameter must be explicitly converted. While the COALESCE function returns the data type of the highest priority values (as in CASE);
• It should be remembered that the expression returned by function ISNULL, is considered by the SQL server as not NULL, and COALESCE on the contrary allowing NULL. This can be seen, for example, when creating a primary key constraint on the calculated column, i.e. using ISNULL(NULL, 1) – this can be done, but not with COALESCE(NULL, 1) (in the examples below we will consider this).

Examples showing the differences between the COALESCE and ISNULL functions

Example 1. Difference in number of parameters.

DECLARE @Var1 INT
DECLARE @Var2 INT
DECLARE @Var3 INT

SET @Var1 = NULL
SET @Var2 = NULL
SET @Var3 = 1

SELECT COALESCE(@Var1, @Var2, @Var3) AS [COALESCE],
ISNULL(@Var1, @Var2) AS [ISNULL] --Error if you specify the third parameter

Example 2. Difference in the returned data type (text data).

DECLARE @Var1 VARCHAR(5)
DECLARE @Var2 VARCHAR(20)

SET @Var1 = NULL
SET @Var2 = 'First parameter NULL'

/*
The function COALESCE will return a value with the type varchar(20),
i.e. like variable @Var2 and ISNULL with type varchar(5)
like the first parameter @Var1, i.e. the data will be truncated
*/
SELECT COALESCE(@Var1, @Var2) AS [COALESCE],
ISNULL(@Var1, @Var2) AS [ISNULL]

Example 3. Difference in the returned data type (integer data type).

DECLARE @Var1 TINYINT
DECLARE @Var2 INT

SET @Var1 = NULL
SET @Var2 = -1

/*
The function COALESCE will return a higher priority data type, i.e. INT,
and the ISNULL function will generate an error,
because you cannot explicitly convert -1 to TINYINT type,
which is the type of first parameter
*/
PRINT COALESCE(@Var1, @Var2)
PRINT ISNULL(@Var1, @Var2)

Example 4. The difference in the return value, i.e. the result ISNULL is not NULL, but the result COALESCE is NULL.

In the example below we create temporary tables with primary key limitation on the calculated column, in the first table the calculated column uses in its expression ISNULL(column1, 1), where column1 admits NULL values, and in the second table COALESCE(column1, 1), i.e. equivalent expressions. As a result, the first table will be created successfully, because the SQL server implies that the returned value of the function ISNULL does not allow values NULL. And when creating the second table will be an error, because in this case, SQL server assumes that the returned value of function COALESCE will be NULL.

-- The table will be created successfully.
CREATE TABLE #TempTable1
(
column1 integer NULL,
column2 AS ISNULL(column1, 1) PRIMARY KEY
)
IF OBJECT_ID('tempdb...#TempTable1') IS NOT NULL
BEGIN
PRINT 'Table #TempTable1 successfully created'
DROP TABLE #TempTable1 - Delete it immediately.
END
GO

-- Error
CREATE TABLE #TempTable2
(
column1 integer NULL,
column2 AS COALESCE(column1, 1) PRIMARY KEY
)

Recommendations for the use of COALESCE and ISNULL functions

The function COALESCE is recommended to use when you just need to return the first value other than NULL.

Use the function ISNULL to replace the value of NULL, if possible its appearance in the expression, to another value, with the second parameter is better to specify an expression 100% different from NULL, such as a constant. In other words, to prevent the situation when we want to use a value and we have a NULL.

If we talk about performance, in cases where the expression is a subquery, and the documentation of the SQL server in the function COALESCE subquery value will be calculated twice, we can conclude that in these cases, the faster will be ISNULL, as it is calculated once. In other cases the difference will be insignificant.

COALESCE and ISNULL In SQL Server