Correlated Subqueries with Outer References

The Logic of Correlated Subqueries

Before we write any code, let's solidify the mental model of how a correlated subquery executes. The previous lesson introduced this, but it's worth visualizing again, as it's the "low-level" logic you're interested in.

The key steps are:

1. The database engine fetches a candidate row from the outer query's source table.
2. It then executes the inner query (the subquery), using the value(s) from the candidate row.
3. The inner query returns a result (in today's lesson, a single value).
4. This result is used in the outer query's WHERE clause to evaluate the condition for the candidate row.
5. This process repeats for every single row in the outer query's table.

This iterative process is what makes correlated subqueries so powerful for dynamic, row-specific comparisons.

The Essential Role of Table Aliases

When writing correlated subqueries, you will almost always be referencing the same table in both the outer query and the inner subquery. To avoid ambiguity, the database needs you to explicitly state which table you're referring to at any given point.

This is accomplished using table aliases. An alias is a temporary, shorthand name you assign to a table within a query. Without them, a correlated subquery is often impossible to write correctly.

A Classic Use Case: Above-Average Performers

Let's tackle a classic problem that perfectly demonstrates the power of a correlated subquery: Find all employees who earn more than the average salary within their own department.

A simple subquery like (SELECT AVG(salary) FROM employees) won't work, as that calculates the average across the entire company. We need the average to be dynamically calculated for each employee's specific department. This is where correlation comes in.

To see how this is done, we'll turn to a video that explains the concept and then provides a clear demonstration of this exact problem.

As the video explained, the query structure looks like this:

SELECT
    emp_name,
    department,
    salary
FROM
    employees e1  -- Outer query alias
WHERE
    salary > (
        SELECT AVG(salary)
        FROM employees e2  -- Inner query alias
        WHERE e2.department = e1.department  -- The CORRELATION
    );

Let's break down the logic:

1. The outer query starts to process the first employee, let's say 'Alice' from the 'HR' department. e1 refers to Alice's row.
2. The subquery is then executed. The WHERE clause e2.department = e1.department becomes e2.department = 'HR'.
3. The subquery calculates the average salary for only the 'HR' department and returns that single value (e.g., 55000).
4. The outer query's WHERE clause compares Alice's salary to this result: WHERE alice_salary > 55000.
5. The process repeats for the next employee, Bob, also in 'HR'. The subquery runs again, returning 55000.
6. When the query gets to Charlie in 'IT', the subquery runs with WHERE e2.department = 'IT', calculating a different average.

This dynamic, row-by-row calculation is the essence of a correlated subquery.

Reinforcing with a Textual Walkthrough and Performance Note

To make sure this concept is crystal clear, let's review a short article that breaks down the same example with sample data. This will allow you to trace the execution at your own pace.

As the article highlights, this power comes at a cost. Because the subquery runs for every row of the outer query, correlated subqueries can be slow on large datasets. If a table has 1 million rows, the subquery will be executed 1 million times! Often, a JOIN or a Common Table Expression (CTE) can achieve the same result more efficiently. We will cover those in upcoming modules, but it's a crucial "job-ready" insight to know that just because a query works, it doesn't mean it's the best way to do it.

SELECT
    product_name,
    price
FROM
    products p1
WHERE
    price > (
        SELECT AVG(price)
        FROM products p2
        WHERE p2.category = p1.category
    );

Beyond Averages: Using Other Aggregates

The pattern isn't limited to AVG(). You can use any aggregate function (MAX, MIN, COUNT, SUM) to create sophisticated, row-specific filters. For example, you could find:

• The movie in each genre with the longest runtime (MAX).
• The product in each category with the lowest price (MIN).
• Salespeople whose sales are below the average for their region (AVG).

Let's watch a final short video that demonstrates this flexibility.

Conclusion

You have now learned the general pattern for writing correlated subqueries. This is a significant step up from simple, independent subqueries and enables a whole new class of analytical questions. By understanding the row-by-row execution logic, you've grasped the "why" behind the syntax.

Key Takeaways:

• A correlated subquery is executed once for every row processed by the outer query.
• It is linked to the outer query via a condition in its WHERE clause that references a column from the outer query's row.
• Table aliases are essential for distinguishing between the inner and outer query's tables.
• This pattern is perfect for comparing a row's value against an aggregate (like AVG, MAX, MIN) calculated for its specific group.
• While powerful, they can be inefficient on large tables. Always consider if a JOIN or CTE might be a better-performing alternative.

Preview of the Next Lesson:
We've now used correlated subqueries in the WHERE clause for both existence checks (EXISTS) and value comparisons. But their utility doesn't end there. In our next lesson, we will see how a correlated subquery can be placed directly in the SELECT clause to create a calculated column, allowing you to fetch and display related data alongside each row in your main result set.