NOT IN.... what a bugger.

Say you've written a query to get a list of employees who have not attended training.

SELECT count(*) FROM employee e
WHERE emp_id NOT IN
(SELECT emp_id FROM trained_emps)

and the result is zero yet you can demonstrate that there are emp_ids in the the employee table which are not in the trained_emps table. What gives?


You need to remember that Oracle uses three value logic, True, False and Null.
1 = 0 is FALSE
1 = 1 is TRUE
1 = NULL is NULL
When you check if something is IN a list, you only need one TRUE for the clause to be TRUE.
When you check if something is NOT IN a list you need ALL FALSE. If you get a NULL that is not a FALSE and therefore it is NOT TRUE that you have ALL FALSE.
If I say, WHERE 1 IN ( 1, 2, 3, 4, NULL), I get 1 TRUE, 3 FALSE, & 1 NULL. I have one TRUE and therefore IN is TRUE.
If I say, WHERE 1 NOT IN ( 2, 3, 4, NULL), I get 3 FALSE, & 1 NULL. I have one NULL and therefore everything isn’t FALSE so NOT IN is FALSE.
However…
NOT EXISTS says that there is no TRUE.
If I say, WHERE 1 NOT EXISTS* ( 2, 3, 4, NULL), I get 3 FALSE, & 1 NULL. I have no TRUE and therefore NOT EXISTS is TRUE.

* You can’t just plug in a list, but you get the drift.
You can also write this without using NOT.