Subqueries Introduced with NOT IN

Subqueries introduced with the keywords 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:

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 the preceding section 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. (The difficulties with interpreting the meaning of joins not based on equality are discussed in more detail in Using Joins to Create Multitable Queries.)