Correlated Subquery Rewriting
A subquery that references the outer row re-executes once per outer row unless the optimizer decorrelates it; rewriting to a JOIN, a LATERAL join, or a window function (e.g. ROW_NUMBER() OVER (PARTITION BY ...) for top-N-per-group instead of a per-row MAX subquery) turns O(n) subquery executions into one set-based pass.
This Concept is waiting for its first lesson!
A subquery that references the outer row re-executes once per outer row unless the optimizer decorrelates it; rewriting to a JOIN, a LATERAL join, or a window function (e.g. ROW_NUMBER() OVER (PARTITION BY ...) for top-N-per-group instead of a per-row MAX subquery) turns O(n) subquery executions into one set-based pass.
Are you a teacher? Sign in to start contributing.
Sign In