Extra: Using where; Using index ... 

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: 

• NULL , if the SELECT  produces any rows 

• FALSE , if the SELECT  produces no rows 

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 : 

•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 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. 

•On the other hand, when outer_expr  is not NULL , it is absolutely essential that this comparison: 

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 

