Usually in SQL queries, you will try to retrieve information from a database by reading specific records from the relevant tables. However, there are situations where what you want to learn from a query is the fact that something is NOT there. So instead of querying for existing records, you are looking for records that do not exist. This article discusses a number of such situations and demonstrates several “anti-queries”. Where possible, I have used the EMP and DEPT table of the classical SCOTT sample schema to illustrate the point.

Some of these examples are perhaps better used to illustrate specific SQL features – such as CONNECT_BY_ISLEAF, PARTITION OUTER JOIN, LISTAGG, MINUS, SCALAR SUBQUERY and more – than to answer the questions at hand.

(more…)