Geek asks and answers

What applications exist for NULLIF ()?

I just had a trivial but genuine use for NULLIF(), for the first time in my career in SQL. Is this a widely used tool that I just ignored, or an almost forgotten SQL quirk? It is present in all major database implementations.

If someone needs an updater, NULLIF(A, B)returns the first value, if it is not equal to the second, in which case it returns NULL. This is equivalent to this expression CASE:

CASE WHEN A <> B OR B IS NULL THEN A END

or in C-style syntax:

A == B || A == null ? null : A

So far, the only non-trivial example I have found is to exclude a specific value from an aggregate function:

SELECT COUNT(NULLIF(Comment, 'Downvoted'))

This has a limitation that allows only to skip one value; a CASE, but more verbose, will allow you to use an expression.

For the record, I found this to suppress the value of the "last change" column if it was equal to the first change:

SELECT Record, FirstChange, NULLIF(LatestChange, FirstChange) AS LatestChange

It was only useful in that it reduced visual clutter for consumers.

+1
source share
5 answers

Three years later, I found stuff for NULLIF: using NULLIF(Field, '')translates empty strings to NULL, which is equivalent to Oracle representing the "NULL" view.

+1
source

I think that

NULLIF(A, B)

- syntactic sugar for

CASE WHEN A = B THEN NULL ELSE A END

But you're right: it's just syntactic sugar that helps the human reader.

+2
source

NULLIF handy when you are working with obsolete data containing a mixture of null values ​​and empty strings.

Example:
SELECT(COALESCE(NULLIF(firstColumn, ''), secondColumn) FROM table WHERE this = that

0
source

SUMand COUNThave the behavior of turning zeros to zeros. I could see that it is NULLIFconvenient when you want to cancel this behavior. If this fact appeared in a recent answer , I presented. If I remembered NULLIF, I would probably write the following

SELECT student, 
       NULLIF(coursecount,0) as courseCount 
FROM   (SELECT cs.student, 
               COUNT(os.course) coursecount 
        FROM   @CURRENTSCHOOL cs 
               LEFT JOIN @OTHERSCHOOLS os 
                 ON cs.student = os.student 
                    AND cs.school <> os.school 
        GROUP  BY cs.student) t
0
source

I often use it when I need to avoid exclusion from a section using Zero:

SELECT
  COALESCE(Expression1 / NULLIF(Expression2, 0), 0) AS Result
FROM …
0
source

More articles:


All Articles