SQL - Difference between COALESCE and ISNULL? [duplicate]

Question

What are the practical differences between COALESCE() and ISNULL(,'')?

When avoiding NULL values in SQL concatenations, which one is the best to be used?

Thanks!

Mcclain

coalesce is defined by the SQL standard and will work on nearly all DBMS. isnull() only works with Microsoft products. – Doldrums

ISNULL takes two parameters, COALESCE can take n parameters, it depends on the requirement and version of SQL Server as to which is preferred – Spit

MySQL also has isnull. – Gunnysack

I found the ISNULL vs COALESCE question before asking this one. But I asked the practical differences when concatenating columns (performance, best practices, code behaviour etc), that's why I created a new question. Sorry if it was a duplicated question. – Mcclain

Answer 1

Comparing COALESCE and ISNULL

The ISNULL function and the COALESCE expression have a similar purpose but can behave differently.

1. Because ISNULL is a function, it is evaluated only once. As described above, the input values for the COALESCE expression can be evaluated multiple times.

2. Data type determination of the resulting expression is different. ISNULL uses the data type of the first parameter, COALESCE follows the CASE expression rules and returns the data type of value with the highest precedence.

3. The NULLability of the result expression is different for ISNULL and COALESCE. The ISNULL return value is always considered NOT NULLable (assuming the return value is a non-nullable one) whereas COALESCE with non-null parameters is considered to be NULL. So the expressions ISNULL(NULL, 1) and COALESCE(NULL, 1) although equivalent have different nullability values. This makes a difference if you are using these expressions in computed columns, creating key constraints or making the return value of a scalar UDF deterministic so that it can be indexed as shown in the following example.

   USE tempdb; 
   GO
   
   -- This statement fails because the PRIMARY KEY cannot accept NULL values
   -- and the nullability of the COALESCE expression for col2 
   -- evaluates to NULL. 
   
   CREATE TABLE #Demo  (  col1 integer NULL,  col2 AS COALESCE(col1, 0) PRIMARY KEY,  col3 AS ISNULL(col1, 0)  ); 
   
   -- This statement succeeds because the nullability of the 
   -- ISNULL function evaluates AS NOT NULL.
   
   CREATE TABLE #Demo  (  col1 integer NULL,  col2 AS COALESCE(col1, 0), col3 AS ISNULL(col1, 0) PRIMARY KEY  );
   

Validations for ISNULL and COALESCE are also different. For example, a NULL value for ISNULL is converted to int whereas for COALESCE, you must provide a data type. ISNULL takes only 2 parameters whereas COALESCE takes a variable number of parameters.

Source: BOL

Tilney

+1. What's more, COALESCE is just a shortcut for a CASE expression, meaning that the optimizer rewrites it to use CASE instead – Doddered

@Benedetto Books OnLine, the official SQL Server documentation. Sorry, not sure it is still called it, but it used to be. – Tilney

Ah, thank you, that makes substantially more sense than my attempts :-) – Benedetto

An example of type handling for (2): – Blumenthal

Answer 2

The main difference is, that COALESCE is ANSI-Standard, so you will also find it in other RDBMSs, the other difference is you can give a whole list of values to be checked to COALESCE whereas to ISNULL you can only pass one.

Danczyk

Answer 3

Because ISNULL is a function, it is evaluated only once. As described above, the input values for the COALESCE expression can be evaluated multiple times. COALESCE basically translates to CASE expression and ISNULL is a built-in implemented in the database engine.

MSDN

Marinate

"ISNULL is faster than COALESCE." [citation needed]. In which cases? Why? – Diverticulum

(correction: faster is not generally applied and was wrong on my part to state something like this( I have removed that statement) but it can be inferred from the documents, i did not faced any such issue with both of them, so please readers use their discretion ) learn.microsoft.com/en-us/sql/t-sql/language-elements/… – Marinate

Answer 4

COALESCE() can have multiple inputs and it will evaluate in order until one of them is not null such as COALESCE(Col1, Col2, Col3, 'N/A'). It's recommended to use this by MS instead of ISNULL()

ISNULL() can only have one input, however it's been shown to be slightly faster than COALESCE.

Fixed