SQLite | SQLite Functions | Returning NULL When Values Match (nullif Function)
The nullif function compares two arguments. It returns NULL when they are equal and returns the first argument when they are not. This article explains how to use the function.
Using the nullif Function
The nullif function compares two values, returning NULL if they are equal and the first value otherwise. Its syntax is as follows.
nullif(value1, value2)
Use it when you want to replace the first argument with NULL if it equals the second argument. For example, specify 0 as the second argument to replace a value of 0 with NULL.
–
Let’s try an example. First, create the following table.
create table score (name text, point integer);
sqlite> create table score (name text, point integer);
sqlite>
Add the following data with INSERT statements.
insert into score values ('devkuma', 84);
insert into score values ('kimkc', 73);
insert into score values ('araikuma', -1);
insert into score values ('happykuma', 91);
sqlite> insert into score values ('devkuma', 84);
sqlite> insert into score values ('kimkc', 73);
sqlite> insert into score values ('araikuma', -1);
sqlite> insert into score values ('happykuma', 91);
sqlite>
Calculate the average of the point column with avg. Without excluding any values, the average is (84 + 73 - 1 + 91) ÷ 4 = 61.75.
select avg(point) from score;
sqlite> .mode column
sqlite> .header on
sqlite>
sqlite> select avg(point) from score;
avg(point)
----------
61.75
sqlite>
To exclude the value -1, use nullif to convert it to NULL before calculating the average. Because avg ignores NULL, the result is (84 + 73 + 91) ÷ 3 = 82.66.
select avg(nullif(point, -1)) from score;
sqlite> select avg(nullif(point, -1)) from score;
avg(nullif(point, -1))
----------------------
82.6666666666667
sqlite>
Use nullif when a particular value should be replaced with NULL.