Optimizing Subqueries, Derived Tables, View References, and Common Table Expressions 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)) 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 The need to evaluate the extra  IS NULL  condition is why MySQL has the  ref_or_null  access method: 1700