Additional ideas for optimizing subqueries #5484

Originally from @jon-chuang on #2248 (comment), copied to new ticket so it didn't get lost:

Some additional ideas for subquery optimizations:

• If rest of subquery is not correlated, push up correlated filter (in particular, correlated equality filter) into a left join on the filter.
• More here: https://www.alibabacloud.com/blog/query-optimization-technology-for-correlated-subqueries_597644

More generally, one can attempt this approach, which is a lot less messy than relying on countless rewriting heuristics (which is also highly error-prone):

• Unnesting arbitrary queries
• Implementation in DuckDB here: https://github.com/duckdb/duckdb/blob/bee8017bdcc5e652aee26ce8cfb260990cf6a369/src/planner/subquery/flatten_dependent_join.cpp#L72