• 15. 12. 2019
• Ing. Jan Zedníček - Data Engineer & Controlling
• 0

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:

1. COALESCE (X,Y) returns full text in VARCHAR(50) data type – it has priority from data precedence point of view
2. ISNULL (X,Y) returns the result in VARCHAR(1) according to first parameter
3. COALESCE(Y,X) returns again VARCHAR(50)
4. 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)`

Rate this post

Ing. Jan Zedníček - Data Engineer & Controlling

My name is Jan Zedníček and I have been working as a freelancer for many companies for more than 10 years. I used to work as a financial controller, analyst and manager at many different companies in field of banking and manufacturing. When I am not at work, I like playing volleyball, chess, doing a workout in the gym.