It is a common occurrence to look for rows in one table
for which there is no matching data in another table—usually for
identifying exceptions. There are two solutions people most often think
of when having to deal with this type of problem: using either
not in ( ) with an uncorrelated subquery or
not exists ( ) with a correlated
subquery. Popular wisdom says that you should use
not exists. Since a correlated subquery is
efficient when used to mop up after the bulk of irrelevant data has been
cleared out by efficient filtering, popular wisdom has it right when the
subquery comes after the strong forces of efficient search criteria, and
totally wrong when the subquery happens to be the only criterion.
One sometimes encounters more exotic solutions to the problem of finding rows in one table for which there is no matching data in another. The following example is a real-life case that monitoring revealed to be one of the costliest queries performed against a database (note that question marks are placeholders, or bind variables , for constant values that are passed to the query on successive executions):
insert into ttmpout(custcode, suistrcod, cempdtcod, bkgareacod, mgtareacod, risktyp, riskflg, usr, seq, country, rating, sigsecsui) select distinct custcode, ?, ?, ?, mgtareacod, ?, ?, usr, seq, country, rating, sigsecsui from ttmpout a where a.seq = ? and 0 = (select count(*) from ttmpout b where b.suistrcod = ? and b.cempdtcod ...