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)
| EXISTS | NOT |