ISNULL and COALESCE are functions used for work with null values (NULL). Both functions return first value out of previously defined entry parameters. There are also some differences between both functions.
ISNULL Syntax:
SELECT ISNULL(<Column>, <Column Replacement - other column or logic>)
FROM dbo.Table
COALESCE Syntax:
SELECT COALESCE(<Column>, <Column Replacement - other column or logic 1>, <Column Replacement - other column or logic 2>,...)
FROM dbo.Table
ISNULL and COALESCE Differences
- ISNULL accepts only two parameters, COALESCE two or more parameters
- ISNULL returns result in data type of entry parameters. If all inserted parameters are NULL, result is NULL
- Data type of COALESCE is evaluated dynamically and it is chosen according to data priority – data precedence. If all entries are NULL, result is error. More on Data precedence in article by Dave Pinal
- ISNULL is faster from performance point of view
ISNULL and COALESCE Examples
We will declare two variables @X and @Y with data type VARCHAR(1) and VARCHAR(50) and then compare different combinations.
DECLARE @X VARCHAR(1) = NULL
DECLARE @Y VARCHAR(50) = 'Functions COALESCE and ISNULL are great'
SELECT
COALESCE(@X, @Y) AS COALESCE_XY,
ISNULL(@X, @Y) AS ISNULL_XY,
COALESCE(@Y, @X) AS COALESCE_YX,
ISNULL(@Y, @X) AS ISNULL_YX
Conclusion:
- COALESCE (X,Y) returns full text in VARCHAR(50) data type – it has priority from data precedence point of view
- ISNULL (X,Y) returns the result in VARCHAR(1) according to first parameter
- COALESCE(Y,X) returns again VARCHAR(50)
- ISNULL (Y,X) returns VARCAHR(50) this time, longer text is inserted as first parameter
Special examples:
-- 1) Result je NULL
SELECT ISNULL(NULL, NULL)
-- 2a) Result is Error
SELECT COALESCE(NULL,NULL)
-- 2b) Result is NULL
DECLARE @X VARCHAR(1) = NULL
DECLARE @Y VARCHAR(50) = NULL
SELECT COALESCE(@X,@Y)