Do you use NULLIF?  For me, this command has been seldom used.  Because of that, I have been dabbling with it a bit lately to make sure I have a better understanding of it.

What is it?

This 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.

Seems pretty simple.

In Action

If you look online at MSDN, you can see a couple of examples and a more complete description of what this function is and does.  The MSDN article can be found here.  I wanted something a little different and decided to visualize it differently for my learning.  So here is what I did.

Code block

WITH randnums AS (
SELECT TOP 100
RowNum = ROW_NUMBER()  OVER (ORDER BY (SELECT 1))
,FirstVal = ABS(CHECKSUM(NEWID()))%10+1
,SecVal = ABS(CHECKSUM(NEWID()))%10+1
FROM Master.dbo.SysColumns t1
CROSS JOIN Master.dbo.SysColumns t2
)
 
SELECT RowNum,FirstVal,SecVal, NULLIF(FirstVal,SecVal) AS 'Null if Equal'
FROM randnums
ORDER BY RowNum

This will give me a nice random sampling of values to compare.  I simply compare the firstval column to the secondval 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.