Correlated Subqueries in SELECT Clauses
You'll learn how to use correlated subqueries in SELECT to create calculated columns that reference the outer query's current row.
The Core Idea
A correlated subquery in the SELECT clause computes a value for each row of your main query by referencing columns from that row. Think of it like adding a personalized calculation column where every row gets its own custom subquery execution.
Unlike non-correlated subqueries (which run once), a correlated subquery in SELECT runs once per row in the outer query, using values from the current row to calculate a result.
How It Works
The subquery must return a single scalar value (one row, one column) because it's being placed in your SELECT list as if it were a regular column. The correlation happens when your subquery references columns from the outer query's current row.
Common pattern:
SELECT
column1,
column2,
(SELECT aggregate_function(...)
FROM other_table
WHERE other_table.id = outer_table.id) AS calculated_value
FROM outer_table;
Practical Example
Imagine you want each employee's salary alongside the average salary in their department:
SELECT
employee_name,
salary,
(SELECT AVG(salary)
FROM employees e2
WHERE e2.department_id = e1.department_id) AS dept_avg_salary
FROM employees e1;
For each employee row, the subquery recalculates the department average using that employee's department_id.
When to Use This
This pattern is useful for row-by-row comparisons, calculating percentages against aggregates, or adding contextual metrics without joining tables (though joins can often be more efficient).
Key Takeaway: Correlated subqueries in SELECT clauses let you add calculated columns that depend on the current row's values, running once per row to return a single scalar result for each.