A subquery that uses not in returns a set of values for each row in the outer query.
If the value in the outer query is not in the set returned by the inner query, the not in evaluates to TRUE, and the outer query puts the record being considered in the results.
However, if the set returned by the inner query contains no matching value, but it does contain a NULL, the not in returns UNKNOWN. This is because NULL stands for “value unknown,” and it is impossible to tell whether the value you are looking for is in a set containing an unknown value. The outer query discards the row. For example:
select pub_name from publishers where $100.00 not in (select price from titles where titles.pub_id = publishers.pub_id)
pub_name ------ New Age Books
New Age Books is the only publisher that does not publish any books that cost $100. Binnet & Handley and Algodata Infosystems were not included in the query results because each publishes a book for which the price is undecided.