Subqueries used with not in

Subqueries that are introduced with the keyword phrase not in also return a list of 0 and higher values. not in means “not = a and not = b and not = c.”

This query finds the names of the publishers who have not published business books, the inverse of the example in “Subqueries used with in”:

select pub_name from publishers 
where pub_id not in 
   (select pub_id 
    from titles 
    where type = "business")
pub_name 
---------------------------------------- 
Binnet & Hardley 

The query is the same as the previous one except that not in is substituted for in. However, you cannot convert this statement to a join; the “not equal” join finds the names of publishers who have published some book that is not a business book. The difficulties interpreting the meaning of joins that are not based on equality are discussed in detail in Chapter 4, “Joins: Retrieving Data from Several Tables.”