Difference between ISNULL and COALESCE

ISNULL and COALESCE

Let us study the differences with the help of a table.

Create a simple table with the columns as below.

CREATE TABLE

ISNULL_COALESCE_NULLIF

(

ID INT,

DATA1 VARCHAR(100),

DATA2 VARCHAR(100)

)

GO

Let us insert the values into the new table exactly as below:

INSERT into ISNULL_COALESCE_NULLIF

values

(1,NULL,NULL),

(2,'Narendra','Modi'),

(3,NULL,'Kejriwal'),

(4,'Avinash','Avinash'),

(5,'Avinash1','Avinash2')

 before proceeding further, we have to remember that for ISNULL and COALESCE functions, only character datatypes (VARCHAR,CHAR)can be passed.

COALESCE

COALESCE returns the first NOT NULL value out of the n number of parameters passed as listed above.

ISNULL

ISNULL Checks whether the first value is NULL or not.

If NULL then returns the string in quotes(2nd parameter)

else returns the value passed in First parameter.

Detailed Differences

NULLIF

Another important NULL function is NULLIF. It is used to compare two values.

If both values are same OR first value is NULL then returns NULL,

else returns the second value