Subquery patterns worth cleaning up before optimization
Structural optimization is not a substitute for indexes or execution plans, but it can remove noise that hides real performance problems. Some subquery shapes are especially good candidates for cleanup before you hand SQL to a reviewer or paste it into a warehouse console.
Pass-through derived tables
A derived table that only re-selects columns from one source—without aggregation, distinct, or inner ordering—is often equivalent to querying the base table directly. Inlining that layer makes filters easier to spot and can help downstream engines reason about predicates earlier.
SELECT o.id
FROM (
SELECT id, customer_id
FROM orders
) o
WHERE o.customer_id = 42;Filters trapped outside the inner scope
When every predicate references only the subquery alias, pushing filters inward can mirror how you would write the query by hand. That is the pattern behind many safe rewrite tools, including the SQL Script optimizer.
Patterns to review manually
- Correlated subqueries where row semantics depend on outer scope.
- Queries with
DISTINCT, window functions, or limits on the inner SELECT. - Dialect-specific operators that parsers may not rewrite reliably.
- Any rewrite that changes alias visibility for later clauses.
Pair cleanup with readable style
Optimization output is easier to audit when indentation and keyword casing are consistent. Use the beautifier after rewrites so diffs in pull requests highlight logic—not spacing churn.
Try the optimizer on a nested query from your backlog and read the rewrite summary before you commit the change.