Q:

'in()' works as expected, but 'not in()' does not

I have a two column table (n and p), where half the n values exist in p and the other half do not. The following code returns 'NULL' instead of returning the half of the n values which are not in p.
select  n
from bst
where n 
not in 
(
    select p from bst
)
order by n
;

But when the keyword 'not' is removed, everything works as expected. Why is this code returning no values?

A:

I strongly recommend using not exists instead of not in with a subquery:
select bst.n
from bst
where not exists select p from bst bst2 where bst2.p = bst.n)
order by bst.n

Why?  Because the two are not equivalent.  If any value of p is NULL, then NOT IN returns no rows at all.  NOT EXISTS has more expected behavior.
Of course, you could fix this problem by filtering in the subquery:
select bst.n
from bst
where bst.n not in (select bst2.p from bst bst2)
order by bst.n;

But I think it is easier just to remember to use not exists rather than not in.

