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

Correlated Subqueries in WHERE Clauses

Use correlated subqueries to filter rows based on calculations or comparisons involving related data.

Correlated Subqueries in WHERE Clauses

What you'll learn: How to use correlated subqueries in WHERE clauses to filter rows based on comparisons with related data from the same or other tables.

The Core Idea

A correlated subquery in a WHERE clause filters rows by referencing columns from the outer query. Unlike non-correlated subqueries that run once and return a result set, correlated subqueries execute once for each row in the outer query, using that row's values to perform row-specific calculations or lookups.

Think of it like a personalized check: instead of applying the same filter to everyone, you're asking "Does this specific row meet a condition that depends on its own values?"

How It Works

The outer query processes each row, passing values to the subquery. The subquery then evaluates a condition specific to that row and returns a result that determines whether the row should be included.

Common Pattern:

SELECT employee_id, salary, department_id
FROM employees e1
WHERE salary > (
    SELECT AVG(salary)
    FROM employees e2
    WHERE e2.department_id = e1.department_id
);

Here, for each employee (e1), the subquery calculates the average salary for that employee's specific department and checks if their salary exceeds it. Notice e1.department_id from the outer query is referenced inside the subquery—that's the correlation.

When to Use

  • Finding rows that exceed department-specific averages
  • Identifying records with values above/below related group metrics
  • Filtering based on existence of related records with specific conditions
  • Comparing individual values against aggregate calculations from related subsets

Key Takeaway: Correlated subqueries in WHERE clauses let you filter rows using dynamic, row-specific comparisons—each row is evaluated against criteria computed specifically for its related data, making them powerful for context-aware filtering.