O'Reilly logo

The Art of SQL by Peter Robson, Stephane Faroult

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Result Set Predicated on Absence of Data

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 ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required