MySQL was able to do a LEFT JOIN optimization on the query and does not examine more rows in this table for the previous row combination after it finds one row that matches the LEFT JOIN criteria. Here is an example of the type of query that can be optimized this way:

SELECT *
FROM t1
LEFT JOIN
t2
ON t1.id = t2.id
WHERE t2.id IS NULL;

Assume that t2.id is defined as NOT NULL. In this case, MySQL scans t1 and looks up the rows in t2 using the values of t1.id. If MySQL finds a matching row in t2, it knows that t2.id can never be NULL, and does not scan through the rest of the rows in t2 that have the same id value. In other words, for each row in t1, MySQL needs to do only a single lookup in t2, regardless of how many rows actually match in t2.

This is exactly our case.

MySQL, as well as all other systems except SQL Server, is able to optimize LEFT JOIN / IS NULL to return FALSE as soon the matching value is found, and it is the only system that cared to document this behavior.

The assumption that t2.id should be defined as NOT NULL, however, is too strong, since a successfull JOIN on equality condition implies that the value found is NOT NULL.

Since MySQL is not capable of using HASH and MERGE join algorithms, the only ANTI JOIN it is capable of is the NESTED LOOPS ANTI JOIN, which is exactly what we see in the query plan (despite the fact that MySQL doesn't call it that). However, this behavior is what an ANTI JOIN does: it checks the values from the left table against only one of each distinct values in the right table, skipping the duplicates.

NOT IN

SELECT l.id, l.value
FROM t_left l
WHERE value NOT IN
(
SELECT value
FROM t_right
)

select `20090918_anti`.`l`.`id` AS `id`,`20090918_anti`.`l`.`value` AS `value` from `20090918_anti`.`t_left` `l` where (not(<in_optimizer>(`20090918_anti`.`l`.`value`,<exists>(<index_lookup>(<cache>(`20090918_anti`.`l`.`value`) in t_right on ix_right_value)))))

This query is as fast as the LEFT JOIN / NOT NULL, however its plan looks quite different.

First, it mentions a DEPENDENT SUBQUERY instead of a second table (which was used in the LEFT JOIN / IS NULL). Nominally, is a dependent subquery indeed, since we don't have a join here but rather a SELECT from a single table with predicate in the WHERE clause.

Second, the description part of the plan mentions this:

<exists>(<index_lookup>(<cache>(`20090918_anti`.`l`.`value`) in t_right on ix_right_value))

What is that?

This is of course our good old friend, the ANTI JOIN. MySQL applies EXISTS optimization to the subquery: it uses the index scan over ix_right_value and returns as soon as it finds (or not finds) a row.

NOT IN is different in how it handles NULL values. However, since both t_left.value and t_right.value are defined as NOT NULL, no NULL value can ever be returned by this predicate, and MySQL takes this into account.

Essentially, this is exactly the same plan that LEFT JOIN / IS NULL uses, despite the fact these plans are executed by the different branches of code and they look different in the results of EXPLAIN. The algorithms are in fact the same in fact and the queries complete in same time.

Field or reference '20090918_anti.l.value' of SELECT #2 was resolved in SELECT #1
select `20090918_anti`.`l`.`id` AS `id`,`20090918_anti`.`l`.`value` AS `value` from `20090918_anti`.`t_left` `l` where (not(exists(select `20090918_anti`.`r`.`value` AS `value` from `20090918_anti`.`t_right` `r` where (`20090918_anti`.`r`.`value` = `20090918_anti`.`l`.`value`))))

This query of course produces the same results.

Execution plan, again, is different. MySQL is the only system that produces three different plans for three different methods.

The plan does not differ much: MySQL does know what an index lookup is and what EXISTS is and it does combine them together.

EXISTS in MySQL is optimized so that it returns as soon as the first value is found. So this query in fact is an ANTI JOIN as well as first two queries are.

This query, however, is a little bit less efficient than the previous two: it takes 0.92 s.

This is not much of a performance drop, however, the query takes 27% more time.

It's hard to tell exact reason for this, since this drop is linear and does not seem to depend on data distribution, number of values in both tables etc., as long as both fields are indexed. Since there are three pieces of code in MySQL that essentialy do one job, it is possible that the code responsible for EXISTS makes some kind of an extra check which takes extra time.

Summary

MySQL can optimize all three methods to do a sort of NESTED LOOPS ANTI JOIN.

It will take each value from t_left and look it up in the index on t_right.value. In case of an index hit or an index miss, the corresponding predicate will immediately return FALSE or TRUE, respectively, and the decision to return the row from t_left or not will be made immediately without examining other rows in t_right.

However, these three methods generate three different plans which are executed by three different pieces of code. The code that executes EXISTS predicate is about 30% less efficient than those that execute index_subquery and LEFT JOIN optimized to use Not exists method.

That's why the best way to search for missing values in MySQL is using a LEFT JOIN / IS NULL or NOT IN rather than NOT EXISTS.

26 Responses to 'NOT IN vs. NOT EXISTS vs. LEFT JOIN / IS NULL: MySQL'

Great article, which I’ve often cited in answers on SO. However, I’m struggling to understand how your conclusion reconciles with [Optimizing Subqueries with `EXISTS` Strategy][1] which (to my reading) suggests that `NOT EXISTS` should be *more efficient* than `NOT IN`? Grateful for your thoughts.

Great article, which I’ve often cited in answers on SO. However, I’m struggling to understand how your conclusion reconciles with [Optimizing Subqueries with `EXISTS` Strategy][1] which (to my reading) suggests that `NOT EXISTS` should be *more efficient* than `NOT IN`? Grateful for your thoughts.

@eggyal: first, NOT EXISTS and EXISTS (anti-join and semi-join) are very different things using different plans. The former cannot be efficiently rewritten using the outer table leading, not with nested loops, the second can.

Second, the article you’re linking concerns the difference in handling NULL values (EXISTS is bivalent, IN is trivalent). This is a very interesting subject however I did not cover it in this article.

Thanks for your article~~!But I still wanna ask a question.Assume that t_left left join t_right by two conditions con1 and con2.There is an unique key created on con1 and con2 like unique key (con1,con2).So,will the execution plan still get a join type “ef_ref” for the following sql like :
SELECT *
FROM t1
LEFT JOIN
t2
ON t1.con1=t2.con1 and t1.con2=t2.con2
WHERE t2.con2 is NULL

I did the test I mentioned above.The execution plan did get a join type “ef_ref”.But you shoule make con1 and con2 the same data type in both t_left and t_right.For example, the same character set and collation when con1 and con2 are varchar

@RickJames: it’s not entirely true. It would repeatedly perform a subquery indeed, but not the subquery. It would push the predicate inside the subquery, so in our case SELECT value FROM t_right WHERE t_right.value = t_left.value is what would be called in a loop, not the original SELECT value FROM t_right. With proper indexing, it works quite well.

Great post. But after testing in my website, I got a strange result.. Screenshot http://i.imgur.com/jLvVnXX.jpg
I have cleared cache (FLUSH QUERY CACHE;) before querying.. dict_en_zhtw have 3.2 million rows, dict_en_zhtw2 have 1000 rows.

Query 1: 0.011 second
SELECT l.dict_id, l.dict_word FROM dict_en_zhtw l
WHERE l.dict_word NOT IN (SELECT dict_word FROM dict_en_zhtw2 r)

Thank you for the article, it solved an empasse I was having.
Actually I got helped by doing the exact contrary of your conclusion:
“That’s why the best way to search for missing values in MySQL is using a LEFT JOIN / IS NULL or NOT IN rather than NOT EXISTS.”

I don’t agree it’s the best way, it’s the more response time efficient and it is the best only if that perfomance is an issue.
For example you didn’t measure the memory aspect of the query.
But most important to me, clarity of code was an issue because I had a stack of joins and negated conditions.
A 30% performance hit in a query executed once a day is equal to zero; in exchange I fixed a bug and left a cleaner, much more semantic code.
Developers are much slower than cpus and much more expensive.