What's the difference between NOT EXISTS vs. NOT IN vs. LEFT JOIN WHERE IS NULL?
SqlSql Problem Overview
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?)
Sql Solutions
Solution 1 - Sql
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.
Solution 2 - Sql
If the database is good at optimising the query, the two first will be transformed to something close to the third.
For simple situations like the ones in you question, there should be little or no difference, as they all will be executed as joins. In more complex queries, the database might not be able to make a join out of the not in
and not exists
queryes. In that case the queries will get a lot slower. On the other hand, a join may also perform badly if there is no index that can be used, so just because you use a join doesn't mean that you are safe. You would have to examine the execution plan of the query to tell if there may be any performance problems.
Solution 3 - Sql
Assuming you are avoiding nulls, they are all ways of writing an anti-join using Standard SQL.
An obvious omission is the equivalent using EXCEPT
:
SELECT a FROM table1
EXCEPT
SELECT a FROM table2
Note in Oracle you need to use the MINUS
operator (arguably a better name):
SELECT a FROM table1
MINUS
SELECT a FROM table2
Speaking of proprietary syntax, there may also be non-Standard equivalents worth investigating depending on the product you are using e.g. OUTER APPLY
in SQL Server (something like):
SELECT t1.a
FROM table1 t1
OUTER APPLY
(
SELECT t2.a
FROM table2 t2
WHERE t2.a = t1.a
) AS dt1
WHERE dt1.a IS NULL;
Solution 4 - Sql
When need to insert data in table with multi-field primary key, consider that it will be much faster (I tried in Access but I think in any Database) not to check that "not exists records with 'such' values in table", - rather just insert into table, and excess records (by the key) will not be inserted twice.
Solution 5 - Sql
Performance perspective always avoid using inverse keywords like NOT IN, NOT EXISTS, ... Because to check the inverse items DBMS need to runs through all the available and drop the inverse selection.