SQL NOT IN

Where column not in (subquery)

In this example, we select all books that do not have sales. Note that this is a non-correlated subquery.

SELECT book_key
FROM book
WHERE book_key NOT IN (SELECT book_key FROM sales);

There are serious problems with subqueries that may return NULL values. It is a good idea to discourage the use of the NOT IN clause (which invokes a subquery) and to prefer NOT EXISTS (which invokes a correlated subquery), since the query returns no rows if any rows returned by the subquery contain null values.

NOT EXISTS example
SELECT b.book_key
FROM book b
WHERE b.book_key NOT EXISTS(SELECT 1 FROM sales s WHERE s.book_key = b.book_key);

Note also that adding the WHERE clause in the subquery will eliminate NULL values in the subquery result set, so the NOT IN would work, but NOT EXISTS is preferred for performance reasons.