Optimizing Subqueries, Derived Tables, View References, and Common Table Expressions The  unique_subquery  and  index_subquery  subquery-specific access methods also have “or  NULL ” variants. The additional  OR ... IS NULL  condition makes query execution slightly more complicated (and some optimizations within the subquery become inapplicable), but generally this is tolerable. The situation is much worse when  outer_expr  can be  NULL . According to the SQL interpretation of  NULL as “unknown value,” NULL IN (SELECT  inner_expr  ...)  should evaluate to: For proper evaluation, it is necessary to be able to check whether the  SELECT  has produced any rows at all, so  outer_expr  =  inner_expr  cannot be pushed down into the subquery. This is a problem because many real world subqueries become very slow unless the equality can be pushed down. Essentially, there must be different ways to execute the subquery depending on the value of  outer_expr . The optimizer chooses SQL compliance over speed, so it accounts for the possibility that  outer_expr might be  NULL : NULL IN (SELECT  inner_expr  FROM ... WHERE  subquery_where ) It is necessary to execute the original  SELECT  here, without any pushed-down equalities of the kind mentioned previously. outer_expr  IN (SELECT  inner_expr  FROM ... WHERE  subquery_where ) Be converted to this expression that uses a pushed-down condition: EXISTS (SELECT 1 FROM ... WHERE  subquery_where  AND  outer_expr = inner_expr ) Without this conversion, subqueries are slow. To solve the dilemma of whether or not to push down conditions into the subquery, the conditions are wrapped within “trigger”  functions. Thus, an expression of the following form: outer_expr  IN (SELECT  inner_expr  FROM ... WHERE  subquery_where ) Is converted into: EXISTS (SELECT 1 FROM ... WHERE  subquery_where AND trigcond( outer_expr = inner_expr )) More generally, if the subquery comparison is based on several pairs of outer and inner expressions, the conversion takes this comparison: ( oe_1 , ...,  oe_N ) IN (SELECT  ie_1 , ...,  ie_N  FROM ... WHERE  subquery_where ) And converts it to this expression: EXISTS (SELECT 1 FROM ... WHERE  subquery_where 1701 •On the other hand, when  outer_expr  is not  NULL , it is absolutely essential that this comparison: •If  outer_expr  is  NULL , to evaluate the following expression, it is necessary to execute the  SELECT  to determine whether it produces any rows: • NULL , if the  SELECT  produces any rows • FALSE , if the  SELECT  produces no rows Extra: Using where; Using index ...