More generally, a comparison of N  values to a subquery that returns N -value rows is subject to the same conversion. If oe_i  and ie_i  represent corresponding outer and inner expression values, this subquery comparison: 

( oe_1 , ..., oe_N ) IN (SELECT ie_1 , ..., ie_N  FROM ... WHERE subquery_where ) 

Becomes: 

EXISTS (SELECT 1 FROM ... WHERE subquery_where AND oe_1  = ie_1 AND ... AND oe_N  = ie_N ) 

For simplicity, the following discussion assumes a single pair of outer and inner expression values. 

The “pushdown”  strategy just described works if either of these conditions is true: 

• outer_expr  and inner_expr  cannot be NULL . 

•You need not distinguish NULL  from FALSE  subquery results. If the subquery is a part of an OR  or AND expression in the WHERE  clause, MySQL assumes that you do not care. Another instance where the optimizer notices that NULL  and FALSE  subquery results need not be distinguished is this construct: 

... WHERE outer_expr  IN ( subquery ) 

In this case, the WHERE  clause rejects the row whether IN ( subquery )  returns NULL  or FALSE . 

Suppose that outer_expr  is known to be a non- NULL  value but the subquery does not produce a row such that outer_expr  = inner_expr . Then outer_expr  IN (SELECT ...)  evaluates as follows: 

• NULL , if the SELECT  produces any row where inner_expr  is NULL 

• FALSE , if the SELECT  produces only non- NULL  values or produces nothing 

In this situation, the approach of looking for rows with outer_expr  = inner_expr  is no longer valid. It is necessary to look for such rows, but if none are found, also look for rows where inner_expr  is NULL . Roughly speaking, the subquery can be converted to something like this: 

EXISTS (SELECT 1 FROM ... WHERE subquery_where  AND ( outer_expr = inner_expr  OR inner_expr  IS NULL)) 

The need to evaluate the extra IS NULL  condition is why MySQL has the ref_or_null  access method: 

mysql> EXPLAIN SELECT outer_expr  IN (SELECT t2.maybe_null_key FROM t2, t3 WHERE ...) FROM t1; *************************** 1. row *************************** id: 1 select_type: PRIMARY table: t1 ... *************************** 2. row *************************** id: 2 select_type: DEPENDENT SUBQUERY table: t2 type: ref_or_null possible_keys: maybe_null_key key: maybe_null_key key_len: 5 ref: func rows: 2 

