NULL vs 0 in integer fields

Question

If a SQLite3 database contains a table with integer fields that may contain 'null' but may also contain the integer 0, how can I differentiate between these values?

To my understanding, SQLite represents the integer zero as 'null' and when I run below select statement, the fields that should contain 0 aren't fetched.

SELECT integer_column 
FROM table_name 
WHERE integer_column IS NOT NULL; 

Also, when I run below query, no rows are returned. (Even though inserts of the value 0 have been done).

SELECT integer_column
FROM table_name
WHERE integer_column = 0;

What is the best way (common practice) to handle this problem in SQLite? (Keep in mind that the column in my table can contain both 'null' and 0 and that neither value should be considered "corrupt data").

Answer 1

This is not a problem; NULL and 0 are distinct:

$ sqlite3
SQLite version 3.8.6 ...
sqlite> CREATE TABLE t(i, name);
sqlite> INSERT INTO t VALUES (0, 'zero');
sqlite> INSERT INTO t VALUES (NULL, 'null');
sqlite> SELECT * FROM t WHERE i = 0;
0|zero
sqlite> SELECT * FROM t WHERE i IS NULL;
|null

If your queries don't return any records, the reason is that the table does not contain any rows with these values.

Answer 2

I'm pretty sure that SQLite does NOT represent 0 as NULL.
If you add A+0 you get A (unless A is NULL). If you add A+NULL you get NULL.

If you test for equality to zero,  you should get the rows where zero has been stored. 
If you test for IS NULL, you should get the rows where NULL has been stored. 
If you test for equality to NULL, you should get nothing. 
If you test for inequality to NULL, you should get nothing.

Standard SQL uses three valued logic when NULLS are involved. Three valued logic is baffling to people who are used to two valued logic.