Where are my NULL records

I got a query from one of our developer few days back regarding a problem that he was facing with the result sets of the SQL. Column in the WHERE clause was having some NULL values. While generating the output, records related to NULL values were not getting generated. So, thought of testing this out and looking for the reasons.

Created a table with three records -

SQL> select * from NULL_TEST;

ID NAME
-- ------------------------------
1 Apple
2 Orange
Watermelon

If I select all the records other than ID=2 then I get the following resutset -

SQL> select * from NULL_TEST where ID!=2;

ID NAME
-- ------------------------------
1 Apple

So, why it is not showing the record that has ID=NULL?

Because, we not use equality ("=") or inequality ("!=") when dealing with NULL; only "IS NULL" or "IS NOT NULL".

Our SQL should be like this -

SQL> select * from NULL_TEST where ID!=2 or ID is NULL;

ID NAME
-- ------------------------------
1 Apple
Watermelon

Thats Better!