Practical SQL Handbook, The: Using SQL Variants, Fourth Edition

NOT EXISTS Seeks the Empty Set

NOT EXISTS is the inverse of EXISTS. NOT EXISTS queries succeed when the subquery returns no rows.

For example, to find the names of publishers that do not publish business books, the query is this:

SQL

select pub_name
from publishers
where not exists
   (select *
    from titles
    where pub_id = publishers.pub_id
    and type = 'business')
pub_name
========================================
Binnet & Hardley
[1 row]

The following query finds the titles for which there have been no sales:

SQL

select title
from titles
where not exists
   (select title_id
    from salesdetails
    where title_id = titles.title_id)
title
========================================================
The Psychology of Computer Cooking
Net Etiquette
[2 rows]