SQLite | SQLite Functions | Returning Another Value for NULL (ifnull and coalesce Functions)
The ifnull function returns its second argument when the first is NULL. The coalesce function provides the same behavior for two or more arguments. This article explains both functions.
Using the ifnull and coalesce Functions
The ifnull function returns the second argument when the first argument is NULL.
ifnull(value1, value2)
The coalesce function returns the first non-NULL argument. If every preceding argument is NULL, it returns the final argument.
coalesce(value1, value2, ..., valueN)
ifnull is equivalent to coalesce with two arguments.
–
Create a table for the examples.
create table user (id integer, name text, address text);
sqlite> create table user (id integer, name text, address text);
sqlite>
Insert the data.
insert into user values (1, 'devkuma', 'Seoul');
insert into user values (2, 'kimkc', NULL);
insert into user values (3, 'araikuma', 'Busan');
insert into user values (4, NULL, 'Paju');
insert into user values (5, NULL, NULL);
sqlite> insert into user values (1, 'devkuma', 'Seoul');
sqlite> insert into user values (2, 'kimkc', NULL);
sqlite> insert into user values (3, 'araikuma', 'Busan');
sqlite> insert into user values (4, NULL, 'Paju');
sqlite> insert into user values (5, NULL, NULL);
sqlite>
Use ifnull to return name when it is not NULL, or 'NoName' otherwise.
select id, ifnull(name, 'NoName') from user;
sqlite> .mode column
sqlite> .header on
sqlite>
sqlite> select id, ifnull(name, 'NoName') from user;
id ifnull(name, 'NoName')
---------- ----------------------
1 devkuma
2 kimkc
3 araikuma
4 NoName
5 NoName
sqlite>
Rows with a name return that value; rows with NULL return the specified fallback.
Use coalesce to return name, then address when name is NULL, and finally 'NoName' when both columns are NULL.
select id, coalesce(name, address, 'NoName') from user;
sqlite> select id, coalesce(name, address, 'NoName') from user;
id coalesce(name, address, 'NoName')
---------- ---------------------------------
1 devkuma
2 kimkc
3 araikuma
4 Paju
5 NoName
sqlite>
The function returns the first non-NULL value, or the supplied fallback when both columns are NULL.