Optimizing Subqueries, Derived Tables, View References, and Common Table Expressions 1694 8.2.2 Optimizing Subqueries, Derived Tables, View References, and Common Table Expressions 8.2.2.1 Optimizing IN and EXISTS Subquery Predicates with Semijoin Transformations The MySQL query optimizer has different strategies available to evaluate subqueries: For a derived table, the optimizer has these choices (which also apply to view references and common table expressions): The following discussion provides more information about the preceding optimization strategies. A limitation on  UPDATE  and  DELETE  statements that use a subquery to modify a single table is that the optimizer does not use semijoin or materialization subquery optimizations. As a workaround, try rewriting them as multiple-table  UPDATE  and DELETE  statements that use a join rather than a subquery. •For a subquery used with an  IN ,  = ANY , or  EXISTS  predicate, the optimizer has these choices: •Semijoin •Materialization • EXISTS  strategy •For a subquery used with a  NOT IN ,  <> ALL  or  NOT EXISTS  predicate, the optimizer has these choices: •Materialization • EXISTS  strategy •Merge the derived table into the outer query block •Materialize the derived table to an internal temporary table SELECT class.class_num, class.class_name FROM class INNER JOIN roster WHERE class.class_num = roster.class_num; A semijoin is a preparation-time transformation that enables multiple execution strategies such as table pullout, duplicate weedout, first match, loose scan, and materialization. The optimizer uses semijoin strategies to improve subquery execution, as described in this section. For an inner join between two tables, the join returns a row from one table as many times as there are matches in the other table. But for some questions, the only information that matters is whether there is a match, not the number of matches. Suppose that there are tables named  class  and  roster  that list classes in a course curriculum and class rosters (students enrolled in each class), respectively. To list the classes that actually have students enrolled, you could use this join: However, the result lists each class once for each enrolled student. For the question being asked, this is unnecessary duplication of information. Assuming that  class_num  is a primary key in the  class  table, duplicate suppression is possible by using SELECT DISTINCT , but it is inefficient to generate all matching rows first only to eliminate duplicates later. Note