Subqueries use the following optimizations to improve performance:
Flattening – converting the subquery to a join
Materializing – storing the subquery results in a worktable
Short circuiting – placing the subquery last in the execution order
Caching subquery results – recording the results of executions
The following sections explain these strategies.
See “showplan messages for subqueries” for an explanation of the showplan messages for subquery processing.