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.