In this lesson, you will learn how to boost database performance by transforming "correlated subqueries"—queries that run repeatedly for every row—into efficient set-based joins.
The Problem with Correlation
A correlated subquery is one that references columns from the outer query. Think of it like a nested loop: for every single row in your main table, the database has to pause, execute the subquery, and then move to the next row. If you have 100,000 rows, your database is effectively performing 100,000 separate, tiny lookups. This creates significant overhead and slows down your analytical queries.
Unrolling into Joins
"Unrolling" is the process of extracting that logic out of the SELECT or WHERE clause and converting it into a JOIN. By doing this, you allow the database engine to use its sophisticated query optimizer to choose the best join algorithm (like a Hash Join or Merge Join), which processes the data in bulk rather than row-by-row.
Example: Before (Correlated Subquery) Imagine you want to find the total sales for every order:
SELECT o.order_id,
(SELECT SUM(i.price) FROM items i WHERE i.order_id = o.order_id) as total_price
FROM orders o;
Example: After (Unrolled Join) By using a join, we calculate the sum once per order and attach it, which is much faster:
SELECT o.order_id, i_summary.total_price
FROM orders o
LEFT JOIN (
SELECT order_id, SUM(price) as total_price
FROM items
GROUP BY order_id
) i_summary ON o.order_id = i_summary.order_id;
Why it Matters
When you unroll a subquery, you are shifting from an "instructional" approach (telling the database to perform a specific loop) to a "declarative" approach (telling the database what data you need). This gives the query planner the flexibility to optimize the execution path based on indexes, table sizes, and system statistics, which is vital when handling large datasets.
Key Takeaway: Replace row-by-row correlated subqueries with joins to allow the database to process data in high-performance batches.