Subqueries with EXISTS vs IN - MySQL

An Explain Plan would have shown you why exactly you should use Exists. Usually the question comes Exists vs Count(*). Exists is faster. Why?

• With regard to challenges present by NULL: when subquery returns Null, for IN the entire query becomes Null. So you need to handle that as well. But using Exist, it's merely a false. Much easier to cope. Simply IN can't compare anything with Null but Exists can.

• e.g. Exists (Select * from yourtable where bla = 'blabla'); you get true/false the moment one hit is found/matched.

• In this case IN sort of takes the position of the Count(*) to select ALL matching rows based on the WHERE because it's comparing all values.

But don't forget this either:

• EXISTS executes at high speed against IN : when the subquery results is very large.
• IN gets ahead of EXISTS : when the subquery results is very small.

Reference to for more details:

• subquery using IN.
• IN - subquery optimization
• Join vs. sub-query.

Method 2 is fast because it is using EXISTS operator, where I MySQL do not load any results.As mentioned in your docs link as well, that it omits whatever is there in SELECT clause. It only checks for the first value that matches the criteria, once found it sets the condition TRUE and moves for further processing.

On the other side Method 1 has IN operator which loads all possible values and then matches it. Condition is set TRUE only when exact match is found which is time consuming process.

Hence your method 2 is fast.

Hope it helps...

The EXISTS operator is a Boolean operator that returns either true or false. The EXISTS operator is often used the in a subquery to test for an “exist” condition.

SELECT     select_listFROM    a_tableWHERE    [NOT] EXISTS(subquery);

If the subquery returns any row, the EXISTS operator returns true, otherwise, it returns false.

In addition, the EXISTS operator terminates further processing immediately once it finds a matching row. Because of this characteristic, you can use the EXISTS operator to improve the performance of the query in some cases.

The NOT operator negates the EXISTS operator. In other words, the NOT EXISTS returns true if the subquery returns no row, otherwise it returns false.

You can use SELECT *, SELECT column, SELECT a_constant, or anything in the subquery. The results are the same because MySQL ignores the select_list that appears in the SELECT clause.

The reason is that the EXISTS operator works based on the “at least found” principle. It returns true and stops scanning table once at least one matching row found.

On the other hands, when the IN operator is combined with a subquery, MySQL must process the subquery first and then uses the result of the subquery to process the whole query.

The general rule of thumb is that if the subquery contains a large volume of data, the EXISTS operator provides a better performance.

However, the query that uses the IN operator will perform faster if the result set returned from the subquery is very small.

For detail explanations and examples: MySQL EXISTS - mysqltutorial.org