Difference between IsNull, Coalesce and NullIf

Overview:

                This article explains the detailed difference between IsNull, Coalesce and NullIf.

ISNULL: This function works like “if” condition which we use in other program languages. It takes only two arguments, if first argument is Null then it returns second and one more thing is that the second argument should be of same size that means if first argument has varchar(2) and second has varchar(4) then it truncates last characters.

Below gives you the structure and the example

ISNULL(argument1, argument2)

Pseudocode

IF argument1 IS Null

                   RETURNS argument2

            ELSE

                   RETURNS argument1

            END      

             Example:             SELECT ISNULL(NULL,'Test')

             Result:                  Test      

 Example :

DECLARE @a VARCHAR(2);

DECLARE @b VARCHAR(4);

SET                          @b = 'abcd';

SELECT ISNULL(@a,@b)

 Result : ab

COALESCE: This function works same as “if else” statement and it takes multiple arguments and it checks Null values for each if first argument is null then it considers the second value, if the both first and second arguments are nulls then it considers third argument. It contradicts the IsNull as it truncates the extra characters if the size of second argument is bigger than first but COALESCE doesn’t. Below are its structure and example

COALESCE(argument1, argument2, argument3[, argument4, argument6, argument7…])

Pseudocode

IF argument1 IS NOT Null 

                RETURNS argument1

            ELSE argument1 IS NOT Null AND argument2 IS Null

                                RETURNS argument1

            ELSE argument1 IS NOT Null AND argument2 IS NOT Null AND argument3 IS Null

                                RETURNS argument1

ELSE argument1 IS Null AND argument2 IS Null AND argument3 IS Null

                                RETURNS “Default Set Value”

            END

                Example:

                Query:

SELECT    field1,

                                                                field2,

                                                                field3,

                                                                COALESCE(field1,field2,field3,0)  AS Result

FROM     TableName

    Result:

    Example :

DECLARE @a VARCHAR(2);

DECLARE @b VARCHAR(4);

SET          @b = 'abcd';

SELECT COALESCE (@a,@b)

    Result : abcd

 NULLIF: This is a special function where it works as “if” statement and it takes two arguments and compares the 2 argument, if both are same then returns “Null” else returns the first argument. Below are its structure and example

NULLIF(argument1, argument2)

Pseudocode

IF argument1 equals argument1

                   RETURNS Null

            ELSE

                   RETURNS argument1

END

Example:             SELECT NullIF(2,1)

Result:                 2

Example:             SELECT NullIF(1,1)

Result:                  NULL