Joe Celko's SQL for Smarties, 3rd Edition

15.3. NOT EXISTS and OUTER JOINs

The NOT EXISTS version of this predicate is almost always used with a correlated subquery. Very often the subquery can be “flattened” into an OUTER JOIN, which will frequently run faster than the original query. Our other sample query was:

SELECT P1.emp_name, ' was born on a day without a famous New
Yorker!'
  FROM Personnel AS P1
 WHERE NOT EXISTS
      (SELECT *
         FROM Celebrities AS C1
        WHERE C1.birth_city = 'New York'
          AND C1.birthday = P1.birthday);

Which we can replace with:

SELECT P1.emp_name, ' was born on a day without a famous New
Yorker!'

  FROM Personnel AS P1
       LEFT OUTER JOIN
       Celebrities AS C1
       ON C1.birth_city = 'New York'
          AND C1.birthday = E2.birthday
  WHERE C1.emp_name IS NULL;

This is assuming that we know each ...