Optimizing Subqueries, Derived Tables, View References, and Common Table Expressions Here,  ot_ i  and  it_ i  represent tables in the outer and inner parts of the query, and  oe_ i  and  ie_ i represent expressions that refer to columns in the outer and inner tables. In MySQL 8.0.17 and later, the subquery can also be the argument to an expression modified by  NOT , IS [NOT] TRUE , or  IS [NOT] FALSE . If a subquery meets the preceding criteria, MySQL converts it to a semijoin (or, in MySQL 8.0.17 or later, an antijoin if applicable) and makes a cost-based choice from these strategies: 1696 SELECT ... FROM ot1, ... WHERE (oe1, ...) IN (SELECT ie1, ... FROM it1, ... WHERE ...); •It must be part of an  IN ,  = ANY , or  EXISTS  predicate that appears at the top level of the  WHERE  or  ON clause, possibly as a term in an  AND  expression. For example: •It must be a single  SELECT  without  UNION  constructs. •It must not contain a  HAVING  clause. •It must not contain any aggregate functions (whether it is explicitly or implicitly grouped). •It must not have a  LIMIT  clause. •The statement must not use the  STRAIGHT_JOIN  join type in the outer query. •The  STRAIGHT_JOIN  modifier must not be present. •The number of outer and inner tables together must be less than the maximum number of tables permitted in a join. •The subquery may be correlated or uncorrelated. In MySQL 8.0.16 and later, decorrelation looks at trivially correlated predicates in the  WHERE  clause of a subquery used as the argument to  EXISTS , and makes it possible to optimize it as if it was used within  IN (SELECT b FROM ...) . The term  trivially correlated  means that the predicate is an equality predicate, that it is the sole predicate in the  WHERE clause (or is combined with  AND ), and that one operand is from a table referenced in the subquery and the other operand is from the outer query block. •The  DISTINCT  keyword is permitted but ignored. Semijoin strategies automatically handle duplicate removal. •A  GROUP BY  clause is permitted but ignored, unless the subquery also contains one or more aggregate functions. •An  ORDER BY  clause is permitted but ignored, since ordering is irrelevant to the evaluation of semijoin strategies. •Convert the subquery to a join, or use table pullout and run the query as an inner join between subquery tables and outer tables. Table pullout pulls a table out from the subquery to the outer query. • Duplicate Weedout : Run the semijoin as if it was a join and remove duplicate records using a temporary table. • FirstMatch : When scanning the inner tables for row combinations and there are multiple instances of a given value group, choose one rather than returning them all. This "shortcuts" scanning and eliminates production of unnecessary rows. • LooseScan : Scan a subquery table using an index that enables a single value to be chosen from each subquery’s value group.