EXPLAIN EXTENDED

From Stack Overflow:

In my first programming job I was told that a query within the IN() predicate gets executed for every row contained in the parent query, and therefore using IN should be avoided.

For example, given the query:

SELECT  COUNT(*)
FROM    Table1
WHERE   Table1Id NOT IN
	(
	SELECT	Table1Id
	FROM	Table2
	WHERE	id_user = 1
	)

Rows	Inner subquery executions
10	10
100	100
1000	1000
10000	10000

Is this correct? How does the IN predicate actually work?

This is the 1^st of 5 articles covering implementation of NOT IN predicate in several RDBMS'es:

• NOT IN in MySQL
• NOT IN in MySQL (using DISTINCT)
• NOT IN in SQL Server
• NOT IN in Oracle
• NOT IN in PostgreSQL

In this article I will describe how MySQL implements this predicate.

Let's create the sample tables:
Read the rest of this entry »