What's the difference between NOT EXISTS vs. NOT IN vs. LEFT JOIN WHERE IS NULL?
sql
Solution
NOT IN vs. NOT EXISTS vs. LEFT JOIN / IS NULL: SQL Server
NOT IN vs. NOT EXISTS vs. LEFT JOIN / IS NULL: PostgreSQL
NOT IN vs. NOT EXISTS vs. LEFT JOIN / IS NULL: Oracle
NOT IN vs. NOT EXISTS vs. LEFT JOIN / IS NULL: MySQL
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.
Problem
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) SELECT a FROM table1 WHERE NOT EXISTS (SELECT * FROM table2 WHERE table1.a = table2.a) SELECT a FROM table1 LEFT JOIN table2 ON table1.a = table2.a WHERE table1.a IS NULL ``` 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?)