Correlated Subqueries and Row-Level Evaluation

In this lesson, you will learn how to write a subquery that communicates directly with its outer query, allowing you to perform calculations that change for every single row you process.

The Concept of Row-Level Evaluation

Up until now, you have mostly used subqueries as independent blocks—you run them once, they return a result set, and that result set is used by the main query. A correlated subquery flips this logic. Instead of being independent, the subquery refers to a column from the outer query.

Think of it like a loop: the database engine takes a row from the outer table, plugs its value into the subquery, evaluates the result, and then moves to the next row. It "evaluates" the logic one row at a time.

A Concrete Illustration

Imagine we have a Products table and an Orders table. We want to find every product that has a price higher than the average price of its own specific category.

SELECT p1.product_name, p1.price
FROM Products p1
WHERE p1.price > (
    SELECT AVG(p2.price)
    FROM Products p2
    WHERE p2.category_id = p1.category_id
);

What is happening here?

1. The engine looks at the first row in Products (let's say it's "Laptop" in "Electronics").
2. The subquery pauses and asks: "What is the average price of all products where the category is 'Electronics'?"
3. The engine calculates that specific average and compares it to the "Laptop" price.
4. It moves to the second row (e.g., "Desk" in "Furniture") and restarts the subquery process, this time calculating the average for "Furniture."

This is powerful because the subquery's result set changes dynamically based on the current row being examined by the outer query. While Joins are usually faster for large datasets, correlated subqueries offer a highly readable way to perform "row-by-row" logic that would be difficult to express with simple group-level aggregations.

Key Takeaway: A correlated subquery acts as a dependent function that recalculates its output for every individual row processed by the outer query.