Metadata
- Author: Mark Raasveldt
- Full Title:: Correlated Subqueries in SQL
- Category:: 🗞️Articles
- Document Tags:: SQL,
- URL:: https://duckdb.org/2023/05/26/correlated-subqueries-in-sql.html
- Finished date:: 2023-05-27
Highlights
correlated subqueries are logically executed once per row. As such, it is natural to think that correlated subqueries are very expensive and should be avoided for performance reasons. While that is true in many SQL systems, it is not the case in DuckDB. In DuckDB, subqueries are always decorrelated. DuckDB uses a state-of-the-art subquery decorrelation algorithm as described in the Unnesting Arbitrary Queries paper. This allows all subqueries to be decorrelated and executed as a single, much more efficient, query (View Highlight)
Note that while it is possible to manually decorrelate certain subqueries by rewriting the SQL, it is not always possible to do so. As described in the Unnesting Arbitrary Queries paper, special join types that are not present in SQL are necessary to decorrelate arbitrary queries. (View Highlight)
In DuckDB, these special join types will be automatically generated by the system to decorrelate all subqueries. In fact, DuckDB does not have support for executing subqueries that are not decorrelated. All subqueries will be decorrelated before DuckDB executes them. (View Highlight)