Subqueries with NOT EXISTS

Subqueries with NOT EXISTS

NOT EXISTS works like EXISTS, except the WHERE clause in which it is used is satisfied if no rows are returned by the subquery.

For example, to find the names of publishers who do not publish business books:

USE pubs
SELECT pub_name
FROM publishers
WHERE NOT EXISTS
   (SELECT *
   FROM titles
   WHERE pub_id = publishers.pub_id
      AND type = 'business')

Here is the result set:

pub_name                                 
---------------------------------------- 
Binnet & Hardley                         
Five Lakes Publishing                    
Ramona Publishers                        
GGG&G                                    
Scootney Books                           
Lucerne Publishing                       

(6 row(s) affected)

This query finds the titles for which there have been no sales.

USE pubs
SELECT title
FROM titles
WHERE NOT EXISTS
   (SELECT title_id
   FROM sales
   WHERE title_id = titles.title_id)

Here is the result set:

title
----------------------------------
The Psychology of Computer Cooking
Net Etiquette

(2 row(s) affected)

See Also

EXISTS

NOT