Scott Duncan-251680 (5/2/2010)
Good question but just raises another for me - when would you use NULLIF? It may be useful, but I am struggling to see where you would use it. Why would you want something to return NULL if two values are equal? Is it just easier than using a CASE statement?
Agreed - very good question. NULLIF is, in my opinion, one of the most under-apprecaited functions in SQL.
Others have already pointed out that you can use it to avoid divide by zero errors. Another use is when a character column that is nullable has erroneously been populated with a mixture of NULLL and blank strings (instead of NULL). Now, if a report should list 'n/a' to represent the missing strings, you can use
COALESCE(NULLIF(ColumnName, ''), 'n/a')
The NULLIF changes empty strings to NULL (and keeps existing NULLs as they are); the COALESCE then replaces them all with 'n/a'.
A third usse is for comparing string columns that are nullable with the requirement that two NULL values should be considered equal. WHERE Column1 = Column2 will miss the NULL pairs. The usual way to work around this is to use
WHERE Column1 = Column2 OR (Column1 IS NULL AND Column2 IS NULL)
This gets awkward if there are other requirements as well, because you need extra parentheses to seperate the OR from the AND, like this:
WHERE Column18 > 27
AND (Column1 = Column2 OR (Column1 IS NULL AND Column2 IS NULL))
Using ISNULL can work around this - although the result is admittedly not trivial to understand:
WHERE Column18 > 27
AND NULLIF(Column1, Column2) IS NULL
AND NULLIF(Column2, Column1) IS NULL
The first NULLIF will return NULL if both columns are equal or Column1 is NULL; the second is NULL if both are equal or Column2 IS NULL. So they are only both NULL if the columns are equal or both are NULL.
As to your last question - it's not just easier than a CASE expression (not statement!), it is in fact the same. NULLIF(expr1, expr2) is defined as shorthand for CASE WHEN expr1 = expr2 THEN NULL ELSE expr1 END.
Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis