Yet Another SQL Best Practice

Just happened to know a best practice to replace NOT IN in SQL using LEFT OUTER JOIN. Consider the following query:

SELECT * FROM a WHERE a.pk NOT IN (SELECT fk_a FROM b)

Nothing's wrong with query above; however, we can make it better by replacing it with:

SELECT * FROM a LEFT OUTER JOIN b ON a.pk = b.fk_a WHERE b.fk_a IS NULL

This can be explained like this: Left outer join uses table "a" as the master. Each row in table "a" will be matched with each row in table "b". If there is no row matched in table "b" for table "a", null will be returned. See an example below:

a b
1 1
2 null
3 3
4 null
5 5

Now in the WHERE clause we state that we are only interested in rows where b.fk_a is null which means we get all rows in table "a" that has not matched any row in table "b".