Optimizing Subqueries, Derived Tables, View References, and Common Table Expressions SELECT * FROM t1 JOIN (SELECT t2.f1 FROM t2) AS derived_t2 ON t1.f2=derived_t2.f1 WHERE t1.f1 > 0; SELECT t1.*, t2.f1 FROM t1 JOIN t2 ON t1.f2=t2.f1 WHERE t1.f1 > 0; 1704 • HAVING • GROUP BY • DISTINCT •Aggregate functions or window functions ( SUM() ,  MIN() ,  MAX() ,  COUNT() , and so forth) •It is possible to disable merging by using in the subquery any constructs that prevent merging, although these are not as explicit in their effect on materialization. Constructs that prevent merging are the same for derived tables, common table expressions, and view references: •Similarly, you can use the  derived_merge  flag of the  optimizer_switch  system variable. See Section 8.9.2, “Switchable Optimizations” . By default, the flag is enabled to permit merging. Disabling the flag prevents merging and avoids  ER_UPDATE_TABLE_USED  errors. •The  MERGE  and  NO_MERGE  optimizer hints can be used. They apply assuming that no other rule prevents merging. See Section 8.9.3, “Optimizer Hints” . •The outer query has this derived table or view reference as the only source in the  FROM  clause. •The outer query does not specify  DISTINCT ,  HAVING , or  ORDER BY . •The outer query is not grouped or aggregated. With merging of the derived table  derived_t2 , that query is executed similar to: With materialization,  derived_t1  and  derived_t2  are each treated as a separate table within their respective queries. The optimizer handles derived tables, view references, and common table expressions the same way: It avoids unnecessary materialization whenever possible, which enables pushing down conditions from the outer query to derived tables and produces more efficient execution plans. (For an example, see Section 8.2.2.2, “Optimizing Subqueries with Materialization” .) If merging would result in an outer query block that references more than 61 base tables, the optimizer chooses materialization instead. The optimizer propagates an  ORDER BY  clause in a derived table or view reference to the outer query block if these conditions are all true: Otherwise, the optimizer ignores the  ORDER BY  clause. The following means are available to influence whether the optimizer attempts to merge derived tables, view references, and common table expressions into the outer query block: The  derived_merge  flag also applies to views that contain no  ALGORITHM  clause. Thus, if an ER_UPDATE_TABLE_USED  error occurs for a view reference that uses an expression equivalent to the subquery, adding  ALGORITHM=TEMPTABLE  to the view definition prevents merging and takes precedence over the  derived_merge  value.