Optimizing Subqueries, Derived Tables, View References, and Common Table Expressions •Temporary table use for materialization is indicated by rows with a  select_type  value of MATERIALIZED  and rows with a  table  value of  <subquery N > . In MySQL 8.0.21 and later, a semijoin transformation can also be applied 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 semijoin transformations are allowed by an optimizer hint or by the  optimizer_switch  setting. 8.2.2.2 Optimizing Subqueries with Materialization The optimizer uses materialization to enable more efficient subquery processing. Materialization speeds up query execution by generating a subquery result as a temporary table, normally in memory. The first time MySQL needs the subquery result, it materializes that result into a temporary table. Any subsequent time the result is needed, MySQL refers again to the temporary table. The optimizer may index the table with a hash index to make lookups fast and inexpensive. The index contains unique values to eliminate duplicates and make the table smaller. Subquery materialization uses an in-memory temporary table when possible, falling back to on-disk storage if the table becomes too large. See Section 8.4.4, “Internal Temporary Table Use in MySQL” . If materialization is not used, the optimizer sometimes rewrites a noncorrelated subquery as a correlated subquery. For example, the following  IN  subquery is noncorrelated ( where_condition  involves only columns from  t2  and not  t1 ): The optimizer might rewrite this as an  EXISTS  correlated subquery: Subquery materialization using a temporary table avoids such rewrites and makes it possible to execute the subquery only once rather than once per row of the outer query. For subquery materialization to be used in MySQL, the  optimizer_switch  system variable materialization  flag must be enabled. (See Section 8.9.2, “Switchable Optimizations” .) With the materialization  flag enabled, materialization applies to subquery predicates that appear anywhere (in the select list,  WHERE ,  ON ,  GROUP BY ,  HAVING , or  ORDER BY ), for predicates that fall into any of these use cases: The following examples illustrate how the requirement for equivalence of  UNKNOWN  and  FALSE  predicate evaluation affects whether subquery materialization can be used. Assume that  where_condition involves columns only from  t2  and not  t1  so that the subquery is noncorrelated. This query is subject to materialization: 1698 •The predicate is  IN  or  NOT IN  and a result of  UNKNOWN  ( NULL ) has the same meaning as a result of FALSE . •The predicate has this form, when there is a single outer expression  oe  and inner expression  ie . The expressions can be nullable. •The predicate has this form, when no outer expression  oe_i  or inner expression  ie_i  is nullable.  N  is 1 or larger. oe  [NOT] IN (SELECT  ie  ...) ( oe_1 ,  oe_2 , ...,  oe_N ) [NOT] IN (SELECT  ie_1 ,  i_2 , ...,  ie_N  ...) SELECT * FROM t1 WHERE EXISTS (SELECT t2.b FROM t2 WHERE  where_condition  AND t1.a=t2.b); SELECT * FROM t1 WHERE t1.a IN (SELECT t2.b FROM t2 WHERE  where_condition );