How to handle null value in sqlite prepared statement

Question

Consider a database, which models a directory tree and each file or folder is a record:

CREATE TABLE "tree" (
"id"    integer,
"parent_id" integer,
"name"  varchar(255),
UNIQUE("parent_id","name"),
PRIMARY KEY("id"),
FOREIGN KEY("parent_id") REFERENCES "tree"("id") on delete cascade
);

The node at root has parent_id set to NULL, since it has no parent. In order to optimize runtime, I want to create a prepared statement to query the tree:

select * from tree where name=? and parent_id=?

The problem is that with the above statement even if I call sqlite3_bind_null, the select statement will not find the rows with parent_id NULL. It only works with the argument parent_id is null.

Currently is prepared two statements: one for NULL and one for != NULL, but that is not very elegant. Is there another way to accomplish this?

Answer 1

You want null-safe equality. In SQLite, you can use is:

select * 
from tree 
where name = ? and parent_id is ?

How this works is explained in the documentation:

The IS and IS NOT operators work like = and != except when one or both of the operands are NULL. In this case, if both operands are NULL, then the IS operator evaluates to 1 (true) and the IS NOT operator evaluates to 0 (false). If one operand is NULL and the other is not, then the IS operator evaluates to 0 (false) and the IS NOT operator is 1 (true).