Differences between SQL Server's ISNULL() and COALESCE() methods. To replace null values with a user-defined value, use the ISNULL and Coalesce functions.

So let's look at an example of how these functions differ in practise.

The SQL Server Management Studio was used to create the example on SQL Server 2012. There are a few distinctions that will be discussed further down.


The ISNULL() method replaces Null with the replacement value supplied.

There are only two arguments in this function.

The ISNULL() function in MySQL is used to determine whether or not an expression is NULL.

ISNULL() takes an expression as a parameter and returns an integer with a value of 0 or 1 based on the parameter.

Example 1: If expression having NULL value

SELECT ISNULL(NULL , 300) As,'Result for sql';
SELECT ISNULL(NULL) As ,'Result for Mysql';

In above two quries return diffrent type of outputs. Because Through condition if values have null thes sql database return replacement value.

If values have null mysql database retun '1' otherwise '0'

Output: The output of above two query is

Result for sql Result for Mysql
300 1

Example 2: If expression does not have NULL value

SELECT ISNULL('TAMIL','WELCOME') As,'Result for sql';
SELECT ISNULL('TAMIL') As,'Result for Mysql';

In above two query value doesn't have any null . The sql database return first value 'TAMIL' Then Mysql database return 'o'

Output: The output of above query is

Result for sql Result for Mysql


COALESCE() is a MySQL function that returns the first non-null value in a list of expressions.

The COALESCE() method returns Null if all of the values in the list evaluate to Null.

The number of parameters isn't limited, but they must all be of the same data type.

The COALESCE() function takes only one parameter, which is a list of possible values.

Example : Implementing COALESCE() function on a list.

--sql-server , mysql , sqlite

In above query returns 'Sunitha' because this is first non null value



SQL Differnce Betweeen ISNULL And COALESCE Function

Here, we discussed the major distiguish in isnull & coalesce functions:-

The ISNULL function in Transact-SQL is a Transact-SQL function. The COALESCE() function follows the ANSI SQL standard.
An expression with non-null parameters that uses ISNULL is deemed NOT NULL. COALESCE expressions with non-null parameters are assumed to be NULL.
There are just two parameters in the ISNULL() function. When using the ISNULL function with more than two parameters, we must utilise nested ISNULL functions.
The COALESCE() method has a lot of options.
The ISNULL() function looks at the first value and limits the second argument value to that length.

Example :

declare @test varchar(3) 
select isnull(@test, 'ABCD') AS ISNULLResult
In the above image, the test variable has length 3. So the ISNULL function returns tes
COALESCE() does not have this restriction.

Example :

  declare @test varchar(3)   
select coalesce(@test, 'ABCD') AS coalesceResult
the COALESCE() function does not; depending on the length, it returns test.
There are several types of parameters in the ISNULL() function.


 DECLARE @a VARCHAR(5)='Hello',  
 @b INT =5   SELECT ISNULL(@a, @b) AS ISNULLResult
The COALESCE() method does not have a limit on the amount of parameters it can accept, but they must all be of the same data type.


 DECLARE @a VARCHAR(5)='Hello',   
The data type returned is the same as the first parameter's data type. The expression with the highest data type precedence is returned as the data type. The result is typed as non-nullable if all expressions are non-nullable.
ISNULL returns the value of the check expression if it is not null, otherwise it returns the value of the second argument. From its parameters, COALESCE returns the first non-null expression. If all of the arguments supplied to COALESCE are null, COALESCE returns null.
The performance of ISNULL and COALESCE is practically identical. ISNULL, on the other hand, is marginally faster than COALESCE. ISNULL is somewhat faster than COALESCE.