Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SQL - Difference between COALESCE and ISNULL? [duplicate]

Tags:

sql

sql-server

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!

like image 353
D. Caan Avatar asked Sep 16 '13 12:09

D. Caan


People also ask

What is the difference between coalesce () and Isnull ()?

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.

Which is better Isnull or coalesce?

advantage that COALESCE has over ISNULL is that it supports more than two inputs, whereas ISNULL supports only two. Another advantage of COALESCE is that it's a standard function (namely, defined by the ISO/ANSI SQL standards), whereas ISNULL is T-SQL–specific.

Which is faster Isnull or coalesce?

ISNULL. Reported result: COALESCE is faster.

Can you use user-defined type in Isnull () or coalesce ()?

The ISNULL and Coalesce functions are both used to replace null values with a user-defined value.


1 Answers

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

like image 71
SchmitzIT Avatar answered Oct 12 '22 11:10

SchmitzIT