SQLite has a function called nullif() and another function called ifnull(), each of which serve a different purpose.

• nullif() allows you to treat certain values as NULL. You can think of it as “return NULL if …”.
• ifnull() allows you to replace NULL values with another value. You can think of it as “if NULL, then …”.

So they basically do the opposite of each other. One replaces NULL values with another value, and the other replaces another value with NULL.

Examples

In some cases, both functions may return the same result. For example:

SELECT
  nullif(1, 2),
  ifnull(1, 2);

Result:

nullif(1, 2)  ifnull(1, 2)
------------  ------------
1             1           

The nullif() function returns a copy of its first argument if the arguments are different and NULL if the arguments are the same. In this case, the arguments are different and it returns the first argument.

The ifnull() function on the other hand, returns a copy of the first non-NULL argument. In this case, both arguments were non-NULL and so the first argument was returned.

With the following example, we start to see the difference between these two functions:

SELECT
  nullif(1, 1),
  ifnull(1, 1);

Result:

nullif(1, 1)  ifnull(1, 1)
------------  ------------
              1           

The nullif() function returns NULL when both arguments were the same. In this case both arguments were the same, and so nullif() did its job and returned NULL.

The ifnull() function returns the first non-NULL argument, and so in this case, it returned the first argument.

The fact that both arguments are the same means nothing to ifnull(). It simply returns the first non-NULL argument. Having said that, if both arguments are NULL then it returns NULL.

Let’s try adding some NULLs.

SELECT
  nullif(NULL, 1),
  ifnull(NULL, 1);

Result:

nullif(NULL, 1)  ifnull(NULL, 1)
---------------  ---------------
                 1              

In this case, nullif() returned NULL because NULL is the first argument (recall that nullif() returns its first argument if both arguments are different).

ifnull() on the other hand returned its first non-NULL argument.

Here’s what it looks like if we switch the arguments around.

SELECT
  nullif(1, NULL),
  ifnull(1, NULL);

Result:

nullif(1, NULL)  ifnull(1, NULL)
---------------  ---------------
1                1              

Both arguments are different and so nullif() returns the first argument.

The first argument is not NULL and so ifnull() returns it.