The SQLite nullif() function is a handy function that allows you to treat certain values as NULL when required.

It’s not to be confused with the ifnull() function, which does the opposite – enables you to treat NULL values as something else.

The nullif() function accepts two arguments, and returns its first argument if the arguments are different and NULL if the arguments are the same.

Syntax

The syntax goes like this:

nullif(X,Y)

The function searches its arguments from left to right for an argument that defines a collating function and uses that collating function for all string comparisons. If neither argument defines a collating function then the BINARY is used.

Example

Here’s a basic example to demonstrate.

SELECT 
  nullif(123,0),
  nullif(0,0),
  nullif(123,123);

Result:

nullif(123,0)  nullif(0,0)  nullif(123,123)
-------------  -----------  ---------------
123                                        

In the first column the two values are different, so the first value is returned.

In the second column both values are the same, so NULL is returned.

The same is true for the third column – both values are the same, so NULL is returned.

A Practical Example

Here’s a database example that demonstrates a more practical usage for the nullif() function.

Take the following table called Products:

ProductId   ProductName    Price     
----------  -------------  ----------
1           Widget Holder  139.5     
2           Widget Stick   89.75     
3           Foo Cap        11.99     
4           Free Widget    0.0       
5           Free Foobar    0.0       
6           Free Beer                

We can see that two products have a price of zero. Another product has a NULL price.

Now, imagine we want to find out how many products have a positive price. In other words, we don’t want to include products that have a price or zero (or NULL for that matter).

To do this, we can use nullif() in conjunction with the count() function.

SELECT count(nullif(Price, 0.00)) 
FROM Products;

Result:

3

We get 3 as expected, which is exactly how many rows have a positive value in the Price column.

This works because the count() function only counts non-NULL values. By converting the zero amounts to NULL, we’re able to ignore those values in our calculation.

Here it is again without the nullif() function.

SELECT count(Price) 
FROM Products;

Result:

5

So in this case, it includes the zero amounts and we get 5. It still ignores row 6 because that actually has a NULL value.