It seems to me that you can do the same thing in a SQL query using either NOT EXISTS, NOT IN, or LEFT JOIN WHERE IS NULL. For example:
SELECT a FROM table1 WHERE a NOT IN (SELECT a FROM table2)
I’m not sure if I got all the syntax correct, but these are the general techniques I’ve seen. Why would I choose to use one over the other? Does performance differ…? Which one of these is the fastest / most efficient? (If it depends on implementation, when would I use each one?)
In a nutshell:
NOT IN is a little bit different: it never matches if there is but a single NULL in the list.
In MySQL, NOT EXISTS is a little bit less efficient
In SQL Server, LEFT JOIN / IS NULL is less efficient
In PostgreSQL, NOT IN is less efficient
In Oracle, all three methods are the same.