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>