On the surface the differences between COALESCE and ISNULL seem minor. In practice there are many differences; some of which are significant. This document attempts to summarize the differences.
- COALESCE is part of the SQL ’92 ANSI standard and is guaranteed to be present on all SQL ’92 compliant database systems (which is probably all existing database platforms).
- COALESCE accepts multiple parameters (the field, and a list of values. The first non-NULL value in the list is the one used).
- COALESCE behaves more like a CASE statement.
- COALESCE promotes its arguments to the highest datatype among compatible arguments (this can produce an error situations where the arguments must be promoted with an explicit CASE).
- The return value’s datatype for COALESCE follows CASE expression rules, and returns the type of the value with the highest precedence:
SELECT COALESCE(5, GETDATE())
Will not return 5, it will return 1900-01-06 00:00:00.000 (which is ‘5’ converted to a datetime value).
- COALESCE(X,Y) is interpreted as:
CASE WHEN X IS NOT NULL THEN X ELSE Y END
If X is a formula, notice how it will be evaluated twice.
- ISNULL is a Microsoft proprietary extension to SQL Server, but is more efficient than COALESCE; only one evaluation is performed.
- ISNULL accepts two parameters (the field, and the value if the field is NULL). If the first value is NULL, ISNULL returns the second value regardless if it’s NULL or non-NULL.
- The return value’s datatype for ISNULL is the same as the first parameter.
- ISNULL can produce undesirable results:
DECLARE @Field1 char(3), @Field2 char(50)
SET @Field2 = 'Some Long String'
SELECT ISNULL(@Field1, @Field2)
Returns ‘Som’ because it maches the datatype of the first expression.
SELECT COALESCE(@Field1, @Field2)
Returns ‘Some Long String’ because it follows CASE expression rules.