NULLIF

NULLIF is a function that compares two values.  If the two values are the same, then the result of the operation is a Null matching the datatype of the first expression.  If the two expressions are different, then the result is the value of the first expression.


Syntax NULLIF(expression , expression)


Example : 

WITH RandomNumber AS (
SELECT TOP 100 RowNumber = ROW_NUMBER()  OVER (ORDER BY (SELECT 1)), 
FirstValue = ABS(CHECKSUM(NEWID()))%10+1 ,SecondValue = ABS(CHECKSUM(NEWID()))%10+1FROM Master.dbo.SysColumns t1 CROSS JOIN Master.dbo.SysColumns t2)
 
SELECT RowNumbe, FirstValue, SecondValue, NULLIF(FirstValue,SecondVal) AS 'Null if Equal' FROM RandomNumber ORDER BY RowNumber

This us giving us a nice random sampling of values to compare. I simply compare the FirstValue column to the SecondValue column.  Both columns are populated with random numbers.  As a part of the result set, I am labeling the comparison field to something descriptive of the field.  I am returning all of the columns so I can see what the values are, and the result of the comparison.  This visualization can help to understand what is happening with the code.  Now I know that if I see a null value, then the two columns are equal.


We can combine NULLIF with COALESCE if you want to test for NULLS and Blanks for example


DECLARE @v VARCHAR(20)
    SELECT @v = ' '
    SELECT COALESCE(NULLIF(@v,' '),'N/A')
Here is another NULLIF example:
CREATE TABLE Blah (SomeCol VARCHAR(33))
    INSERT Blah VALUES(NULL)
    INSERT Blah VALUES('')
    INSERT Blah VALUES(' ')
    INSERT Blah VALUES('A')
    INSERT Blah VALUES('B B')
    --Using COALESCE and NULLIF
    SELECT COALESCE(NULLIF(RTRIM(SomeCol),' '),'N/A')
    FROM Blah
    --Using CASE
    SELECT CASE WHEN RTRIM(SomeCol) = '' THEN 'N/A'
    WHEN RTRIM(SomeCol) IS NULL THEN 'N/A'
    ELSE SomeCol END SomeCol
    FROM Blah