NULL and NOT IN (Oracle)

Here is a very interesting scenario.

I have an employee table called EMP.

And I have a DEPT table.

Now, I write a query to find out all the employees who are part of any department. (DEPT_ID is properly updated and found in DEPT table)

Now, I want to know if any employee is not part of any department.

What??? No results? I know that employee with EMP_ID 9 doesn’t belong to any department. Where did he go?

Is Oracle drunk? Is it giving me wrong results? Wait let me check with another query.

Ah.. there you see. EMP_ID 9 is there after all. So… what was wrong with the previous query?

Wait.. let me see if I can get all the employees who are not part of DEPT_NO 3.

What happened to EMP_ID 9 again? He is not part of DEPT_NO 3 (for that matter, he is not part of any dept). Why didn’t Oracle recognize him? Something is definitely wrong.

Consider the record with EMP_ID 1. If I rewrite the query with the values

Select * from EMP where 1 NOT IN (1, 2, 3).

1 NOT IN (1, 2, 3) is false. So it doesn’t return the record.

Now… consider the record with the issue. It has NULL for DEPT_NO. Let me rewrite the query with the values

Select * from EMP where NULL NOT IN (1, 2, 3)

NULL NOT IN (1, 2, 3) is NULL. It doesn’t equate to TRUE.

A record will be returned only if the condition equates to TRUE. Since it equated to NULL, the record was not returned. 

Lesson learnt: Be careful when using NOT IN when there are NULL values. Always use NOT EXISTS with a co-related sub-query.