Michel Bruggeman

Wednesday, January 18, 2012

The usage of ISNULL and NULLIF

Explanation of the NULLIF:

Syntax: NULLIF ( expression1, expression2)

NULLIF returns a null value if the two specified
expressions are equal. NULLIF returns the first expression if the two
expressions are not equal. If the expressions are equal, NULLIF returns a null
value of the type of the first expression. NULLIF is equivalent to a searched
CASE function in which the two expressions are equal and the resulting
expression is NULL.

Replaces NULL with the specified replacement value. The value of
check_expression is returned if it is not NULL; otherwise, replacement_value is
returned after it is implicitly converted to the type of check_expression, if
the types are different.

Following is good example of ISNULL from BOL:

USE AdventureWorks;GOSELECT AVG(ISNULL(Weight, 50)) FROM Production.Product;GO

An interesting observation is that NULLIF returns null if it comparison is successful,
where as ISNULL returns not null if its comparison is successful. In one way
they are opposite to each other.

About Me

Welcome to my weblog. My name is Jacob Buter, I am SQL Server DBA in Hartford, CT. I have more almost 25 years experience in the IT. I have worked with Oracle for about 6 years and the last 10 years I have been a SQL Server DBA.
Check in frequently for tips.