COALESCE(), ISNULL(), NVL()

ISNULL (MS SQL Server), NVL(Oracle) functions are used to replace null values with user defined value in case the user wants to treat null value differently. COALESCE() function is a more generalized form of NVL() or ISNULL() function and is an ANSI standard where as the others are Oracle/SQL Server proprietary functions. It exists in all of the major RDBMS (Oracle, SQL Server and DB2). Basic difference between COALESCE and their counter parts is that COALESCE returns first non null value as it can take more than 2 expressions or values as an argument while ISNULL or NVL takes only two arguments. First argument is expression or column name and second argument is expression or constant with which we want to replace first argument if it is null. Let us create table first and populate it with some data (Oracle syntax is used for this illustration):

Usage of Coalesce(), ISNULL() and NVL() functions:

CREATE TABLE TEST
(
TEST_ID NUMBER(9),
TEST_COL1 NUMBER(9),
TEST_COL2 NUMBER(9),
TEST_NAME VARCHAR(30)
)
/

INSERT INTO TEST VALUES(1,1000,1101,’Oracle’);
INSERT INTO TEST VALUES(2,2000,NULL,’SQLServer’);
INSERT INTO TEST VALUES(3,NULL,3000,’DB2′);
INSERT INTO TEST VALUES(4,NULL,4000,’Sybase’);
INSERT INTO TEST VALUES(5,NULL,NULL,’Informix’);
INSERT INTO TEST VALUES(6,NULL,NULL,’MYSQL’);
COMMIT;

Following is the result of the select statement using NVL. As mentioned earlier, it takes only two arguments. If you try to specify more than two arguments, Oracle will return an ORA-00909 (Invalid number of arguments error).

SQL> SELECT TEST_NAME,NVL(TEST_COL1,TEST_COL2) FROM TEST;

TEST_NAME NVL(TEST_COL1,TEST_COL2)
—————————— ————————
Oracle 1000
SQLServer 2000
DB2 3000
Sybase 4000
Informix
MYSQL

If you look at the above result set, you will notice that, wherever TEST_COL1 value is null, query returned TEST_COL2 value. For Informix and MYSQL both the column values are null hence it returned null. Now let us execute similar statement using COALESCE. Using COALESCE we want to get data in a manner where if TEST_COL1 value is null the column value of TEST_COL2 should be returned and if TEST_COL2 value is also null then constant value 9999 should be returned.

SQL> SELECT TEST_NAME,COALESCE(TEST_COL1,TEST_COL2,9999) FROM TEST;

TEST_NAME COALESCE(TEST_COL1,TEST_COL2,9999)
—————————— ———————————-
Oracle 1000
SQLServer 2000
DB2 3000
Sybase 4000
Informix 9999
MYSQL 9999

In the above SQL, we have supplied three arguments to COALESCE function and it returned first not null value provided at least one expression or column contains non-null value. If all expression values are null then final result will be null. Above SQL statement is equivalent to following case statement.

SELECT TEST_NAME,
CASE WHEN TEST_COL1 IS NOT NULL THEN TEST_COL1
ELSE COALESCE (TEST_COL2,9999)
END as first_Non_Null
FROM TEST
/

Upon execution, above statement will also return the same value as statement with COALESCE. It hides the complexity of the case statement when there are more than 3 arguments involved. One thing to remember when COALESCE is used is that all the expressions should be of the same data type or must be implicitly convertible to same data type other wise it will give an error.

Please do remember that if you use functions on indexed columns in the filter criteria or in the join conditions, then that will negate the usage of the index. There are ways to get that to work (using FBI in Oracle and computed columns in SQL Server or expression generated columns in DB2 LUW) if you really need to do it.

Other Differences that you should be aware of:

• Another thing of importance is that when you use ISNULL(), it looks at the first value and the second parameter value is automatically limited to that length – COALESCE() does not have this restriction. Here is an example (T-SQL Syntax):

declare @t varchar(1)
set @t = NULL
select isnull (@t, ‘ABCD’)
select coalesce (@t, ‘ABCD’)

The ISNULL() function returns ‘A’ whereas coalesce will return ‘ABCD’. One needs to keep this in mind else you might get results that you never expected.

• And just like the UNION/UNION ALL post that we did about implicit data-type conversions leading to issues, implicit data type conversions can create headaches here as well. In the case of the COALESCE() function, if the values are of different data-types, you can get errors as well as wrong results. Example:

select coalesce(‘test’, 100)

This results into an error:

Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value ‘test’ to data type int.

Likewise: select coalesce(12345678910, current_timestamp) will give:

Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type datetime.

And this execution:

select coalesce(100, current_timestamp)

will give back wrong results due to the implicit conversion. You will get: “1900-04-11 00:00:00.000” instead of 100.

So, you need to be aware that when using COALESCE(), all the values/expressions should be the same data-types or should be implicitly convertible to the same data-type.

• Also, if you have a select statement as an argument to these functions, there are performance implications that you need to be aware of – there is a discussion thread in one of the groups about it – you can read more on that here.