Difference between NULLIF(), ISNULL() and COALESCE()

ISNULL and NULLIF are exactly opposite to each other.

ISNULL returns NULL if comparison becomes successful.

On the other hand, NULLIF returns not null if comparison becomes successful.

COALESCE()

It returns the first nonnull expression among its arguments

Syntax:

COALESCE (expression1,....2,...3, etc)

SELECT COALESCE(NULL,NULL,10,NULLL,20,30)

Output:10

ISNULL and COALESCE though equivalent, can behave differently.

An expression involving ISNULL with non-null parameters is considered to be NOT NULL,

while expressions involving COALESCE with non-null parameters is considered to be NULL.