as found on http://www.informit.com/articles/article.aspx?p=30875&seqNum=5

Rewriting Subselects That Select Non-Matching (Missing) Values

Another common type of subselect query searches for values in one table that are not present in another table. As we've seen before, the "which values are not present" type of problem is a clue that a LEFT JOIN may be helpful. The following is a query with a subselect that tests for students who are not listed in the absence table (it finds those students with perfect attendance):

SELECT * FROM student
WHERE student_id NOT IN (SELECT student_id FROM absence);

This query can be rewritten using a LEFT JOIN as follows:

SELECT student.*
FROM student LEFT JOIN absence ON student.student_id = absence.student_id
WHERE absence.student_id IS NULL;

In general terms, the subselect query form is as follows:

SELECT * FROM table1
WHERE column1 NOT IN (SELECT column2 FROM table2);

A query having that form can be rewritten like this:

SELECT table1.*
FROM table1 LEFT JOIN table2 ON table1.column1 = table2.column2
WHERE table2.column2 IS NULL;

This assumes that table2.column2 is declared as NOT NULL.