Optimizing Subqueries, Derived Tables, View References, and Common Table Expressions Here, it does not matter whether the  IN  predicate returns  UNKNOWN  or  FALSE . Either way, the row from  t1 is not included in the query result. An example where subquery materialization is not used is the following query, where  t2.b  is a nullable column: The following restrictions apply to the use of subquery materialization: Use of  EXPLAIN  with a query provides some indication of whether the optimizer uses subquery materialization: In MySQL 8.0.21 and later, MySQL can also apply subquery materialization to a single-table  UPDATE or  DELETE  statement that uses a  [NOT] IN  or  [NOT] EXISTS  subquery predicate, provided that the statement does not use  ORDER BY  or  LIMIT , and that subquery materialization is allowed by an optimizer hint or by the  optimizer_switch  setting. Certain optimizations are applicable to comparisons that use the  IN  (or  =ANY ) operator to test subquery results. This section discusses these optimizations, particularly with regard to the challenges that  NULL values present. The last part of the discussion suggests how you can help the optimizer. Consider the following subquery comparison: MySQL evaluates queries “from outside to inside.”  That is, it first obtains the value of the outer expression outer_expr , and then runs the subquery and captures the rows that it produces. A very useful optimization is to “inform”  the subquery that the only rows of interest are those where the inner expression  inner_expr  is equal to  outer_expr . This is done by pushing down an appropriate equality into the subquery’s  WHERE  clause to make it more restrictive. The converted comparison looks like this: After the conversion, MySQL can use the pushed-down equality to limit the number of rows it must examine to evaluate the subquery. 1699 8.2.2.3 Optimizing Subqueries with the EXISTS Strategy •The types of the inner and outer expressions must match. For example, the optimizer might be able to use materialization if both expressions are integer or both are decimal, but cannot if one expression is integer and the other is decimal. •The inner expression cannot be a  BLOB . •Compared to query execution that does not use materialization,  select_type  may change from DEPENDENT SUBQUERY  to  SUBQUERY . This indicates that, for a subquery that would be executed once per outer row, materialization enables the subquery to be executed just once. •For extended  EXPLAIN  output, the text displayed by a following  SHOW WARNINGS  includes materialize  and  materialized-subquery . SELECT * FROM t1 WHERE (t1.a,t1.b) NOT IN (SELECT t2.a,t2.b FROM t2 WHERE  where_condition ); SELECT * FROM t1 WHERE t1.a IN (SELECT t2.b FROM t2 WHERE  where_condition ); outer_expr  IN (SELECT  inner_expr  FROM ... WHERE  subquery_where ) EXISTS (SELECT 1 FROM ... WHERE  subquery_where  AND  outer_expr = inner_expr )