Correlated Subqueries

Correlated Subqueries

A subquery is a query nested inside another query, enclosed in parentheses.

A correlated subquery references columns from the outer query and is re-evaluated for each row of the outer query.

💡 A correlated subquery lets you use the result of one query as input for another — like asking a question about the answer to a previous question.

Basic Syntax

SELECT column1, column2
FROM table1
WHERE column3 > (
    SELECT AVG(column3) FROM table2 WHERE table2.column = table1.column
);

Example

SELECT first_name, last_name, salary
FROM employees e1
WHERE salary > (
    SELECT AVG(salary)
    FROM employees e2
    WHERE e2.department = e1.department
);

✅ Key Takeaways

1. Subqueries must be enclosed in parentheses
2. Correlated subqueries run once per row of the outer query
3. Can be used in SELECT, FROM, WHERE, and HAVING
4. Correlated subqueries can often be rewritten as JOINs for better performance
5. EXISTS is more efficient than IN for large datasets
6. Subqueries in FROM must have an alias