Subqueries with NOT IN

Subqueries with NOT IN

Subqueries introduced with the keyword NOT IN also return a list of zero or more values.

This query finds the names of the publishers who have not published business books.

USE pubs
SELECT pub_name
FROM publishers
WHERE pub_id NOT IN
   (SELECT pub_id
   FROM titles
   WHERE type = 'business')

The query is exactly the same as the one in Subqueries with IN, except that NOT IN is substituted for IN. However, this statement cannot be converted to a join. The analogous not-equal join has a different meaning: It finds the names of publishers who have published some book that is not a business book. For information about interpreting the meaning of joins not based on equality, see Joining Three or More Tables.