Oracle Performance and Engineering

Often, tuning is just iterative. You try on a few possible options till it clicks. Then, you often analyze to learn why something worked or why the other didn't. Replacing NOT EXISTS by OUTER JOIN (like EXISTS with INNER JOIN) can be one such iterative option --

The code

SELECT au_lname, au_fname
   FROM   authors AS a
WHERE NOT EXISTS (
      SELECT *
      FROM   titleauthor AS ta
      WHERE  ta.au_id = a.au_id)

is equivalent to -

SELECT a.au_lname, a.au_fname
FROM      authors AS a
LEFT JOIN titleauthor AS ta
ON   ta.au_id = a.au_id
WHERE     ta.au_id IS NULL

In the old pre-ANSI OUTER JOIN syntax this looks like --

SELECT a.au_lname, a.au_fname
FROM      authors AS a,
       titleauthor AS ta
WHERE     ta.au_id IS NULL
and    ta.au_id(+) = a.au_id

It's TA is OUTER JOINED with A -- and for every record where TA doesn't have a value for AU_ID! Same as NOT EXISTS.