SELECT * FROM t1 JOIN (SELECT t2.f1 FROM t2) AS derived_t2 ON t1.f2=derived_t2.f1 WHERE t1.f1 > 0; 

With merging of the derived table derived_t2 , that query is executed similar to: 

SELECT t1.*, t2.f1 FROM t1 JOIN t2 ON t1.f2=t2.f1 WHERE t1.f1 > 0; 

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: 

•The outer query is not grouped or aggregated. 

•The outer query does not specify DISTINCT , HAVING , or ORDER BY . 

•The outer query has this derived table or view reference as the only source in the FROM  clause. 

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 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” . 

•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 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. 

•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: 

•Aggregate functions or window functions ( SUM() , MIN() , MAX() , COUNT() , and so forth) 

• DISTINCT 

• GROUP BY 

• HAVING 

