Course contentsShow
← Data Science
Lesson 978 of 2,14525. SQL Subqueries and CTEsFree lesson

Correlated Subqueries in SELECT Clauses

Return scalar values from correlated subqueries as calculated columns in your result set.

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.