IsNull and NullIf

IsNull and NullIf sounds same but has different fundtion in SQL.
IsNull replaces Null with the specified argument.
NullIf returns NULL if the specified two argument are equal.
Example:
DECLARE @String nValchar(15) = ''
SELECT NullIf(LEN(@String), 0) 
-- This will return Null because specified variable's length is 0 and both parameters are equal.
SELECT IsNULL(@String, 'No Value')
-- This will return '' string because @String is not Null.

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