NULLIF - ISNULL - COALESCE

One thing which I always need more information is the NULL, because it is very tricky. Here is just a simple notes….

NULLIF (expr1, expr2)

Returns null value when both expression are equal; if not it returns the first expression

NULLIF is equivalent to the case statement.

Example from BOL:


USE AdventureWorks;
GO

SELECT ProductID, MakeFlag, FinishedGoodsFlag,
NULLIF(MakeFlag,FinishedGoodsFlag)AS 'Null if Equal'
FROM Production.Product
WHERE ProductID < 10;
GO

SELECT ProductID, MakeFlag, FinishedGoodsFlag,'Null if Equal' =
CASE
WHEN MakeFlag = FinishedGoodsFlag THEN NULL
ELSE MakeFlag
END
FROM Production.Product
WHERE ProductID < 10;
GO
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

ISNULL (expr_check, expr_replace)

Replaces null value with the replacement value.

USE AdventureWorks;
GO
SELECT ProductID,(ISNULL(Weight, 10))
FROM Production.Product;
GO
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

COALESCE (expr1...exprn)

Returns first non-null expression among its arguments


USE AdventureWorks;
GO
SELECT ProductID,(COALESCE(Weight, 10))
FROM Production.Product;
GO


Difference between ISNULL and COALESCE

ISNULL takes only 2 parameters. COALESCE can take any number of parameters.

ISNULL returns the datatype of first parameter whereas the return type of COALESCE is determined
by the data precedence rules; explicit casting should be done to get the desired data type.

For example the following two statements give different results

DECLARE @var varchar(2) -- Please use meaningful variable names in real code
SET @var = null
SELECT ISNULL( @var, 'Test' )
SELECT COALESCE( @var, 'Test' )