sqlite3, if it's not null, it's not 0, and it's not "", what is it?

Question

In sqlite3, I've used the following syntax to insert rows into a table:

INSERT INTO schedule VALUES(some_date, (SELECT id_from_another_table WHERE where_clause));

What seems like the obvious assumption is that this embedded (SELECT ...) query will return a NULL when its result set is empty.

However, that does not seem to be the case...

sqlite> .schema schedule
CREATE TABLE schedule(date date, agent_id integer);

sqlite> select * from schedule;
2014-01-09|22
2014-01-09|
2014-01-09|23
2014-01-09|24

Notice how the second row is missing an entry for agent_id?

sqlite> select * from schedule where agent_id = null;
#nothing

sqlite> select * from schedule where agent_id = 0;
#nothing

sqlite> select * from schedule where agent_id = "";
#nothing

sqlite> select * from schedule where agent_id LIKE "% %";
#nothing

If that agent_id doesn't match null, "", 0, or anything with a space in it, what the heck is it?

Thanks in advance! :-)

Answer 1

In all other DBs I've worked with the syntax would be column IS NULL. From what I can see it is the same in sqlite.

select * from schedule where agent_id IS NULL;