Optimizing Subqueries with Materialization

How the optimizer handles complex subqueries.

The optimizer creates a temporary internal table for the subquery result to avoid repeated execution of the subquery for every outer row.