WHERE - IS NULL not working in SQLite?

Question

a IS b and a IS NOT b is the general form where a and b are expressions.

This is generally only seen in a IS NULL and a IS NOT NULL cases. There are also ISNULL and NOTNULL (also NOT NULL) operators which are short-hands for the previous expressions, respectively (they only take in a single operand).

The SQL understood in SQLite expressions is covered in SQLite Query Language: Expressions.

Make sure that (previous) statements have been terminated with a ; first if using the CLI.

These are all valid to negate a "null match":

expr NOT NULL
expr NOTNULL
expr IS NOT NULL

These are all valid to "match null":

expr ISNULL
expr IS NULL

Since all of the above constructs are themselves expressions the negations are also valid (e.g. NOT (expr NOT NULL) is equivalent to expr IS NULL).

Happy coding.

---

The proof in the pudding:

SQLite version 3.7.7.1 2011-06-28 17:39:05
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> create table x (y int null);
sqlite> select * from x where y isnull;
sqlite> select * from x where y notnull;
sqlite> select * from x where y not null;
sqlite> select * from x where y is null;
sqlite> select * from x where y is not null;
sqlite>

The problem could stem from how SQLite handles empty columns. For instance just because a column is empty does not mean it is NULL. Have you tested against ""?

SELECT * FROM project WHERE parent_id = ""

That query might return results.

In Android SQLite, field IS NULL doesn't work either.

field = 'null' does. Give it a try in your environment

This works on SQLite in SQLite Manager for Firefox:

          select * from foo where not baz is not null

The query above returns rows where column [baz] is null. :-) Yarin, maybe it will work for you? (The 'not' before the column name is not a typo).

This query too finds rows where baz is null:

         select * from foo where [baz]  is  null