SQLite | Query Data | Find Rows with NULL Values (IS NULL)
The IS NULL clause lets you test whether a column contains NULL. This article explains how to use IS NULL in conditional expressions.
For details about the WHERE clause, see Set Query Conditions (WHERE Clause).
Writing Conditions with IS NULL
Use IS NULL to test whether a column value is NULL. The syntax is as follows.
SELECT column_name, ... FROM table_name WHERE column_name IS NULL;
This returns only rows whose column value is NULL. You can also use ISNULL instead of IS NULL.
To retrieve rows whose column value is not NULL, use IS NOT NULL.
SELECT column_name, ... FROM table_name WHERE column_name IS NOT NULL;
This returns only rows whose column value is not NULL. SQLite also accepts NOTNULL and NOT NULL.
–
Now try it with an example. Create the following table.
create table user (id integer, name text, address text);
sqlite> create table user (id integer, name text, address text);
sqlite>
Insert data into the table with INSERT statements.
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, 'happykuma', 'Daejeon');
insert into user values (5, 'mykuma', 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, 'happykuma', 'Daejeon');
sqlite> insert into user values (5, 'mykuma', null);
sqlite>
Querying the table without a condition returns the following rows. Some values in the address column are NULL.
select * from user;
sqlite> select * from user;
id name address
---------- ---------- ----------
1 devkuma Seoul
2 kimkc
3 araikuma Busan
4 happykuma Daejeon
5 mykuma
sqlite>
Use IS NULL to retrieve only rows where address contains NULL.
select * from user where address is null;
sqlite> select * from user where address is null;
id name address
---------- ---------- ----------
2 kimkc
5 mykuma
sqlite>
The result contains only rows with a NULL address.
Next, use IS NOT NULL to retrieve rows whose address value is not NULL.
select * from user where address is not null;
sqlite> select * from user where address is not null;
id name address
---------- ---------- ----------
1 devkuma Seoul
3 araikuma Busan
4 happykuma Daejeon
sqlite>
The result contains only rows without a NULL address.
Comparing a column with NULL using the equality operator (=) does not return the expected result. Consider these SELECT statements.
select * from user where address = null;
select * from user where address = NULL;
select * from user where address = '';
sqlite> select * from user where address = null;
sqlite> select * from user where address = NULL;
sqlite> select * from user where address = '';
sqlite>
None of these queries returns a row. To test whether a value is NULL, use IS NULL.