Correlated subqueries

Lesson Description

Write correlated subqueries that evaluate each row against its own context. This lesson helps you solve comparison-style problems while staying precise about row scope and performance tradeoffs.

Easy Project

Mini project: Turn this lesson into a real-world query artifact by writing one clean business report query and validating output quality.

A correlated subquery references a column from the outer query. It re-executes for each row of the outer query.

Example: employees earning above their department average

SELECT first_name, last_name, salary, department_id
FROM employees e
WHERE salary `>` (
  SELECT AVG(salary)
  FROM employees inner_e
  WHERE inner_e.department_id `=` e.department_id  -- references outer row
);

e.department_id links the inner query to the current outer row. For each employee, the inner query calculates that employee's department average.

How it differs from a regular subquery

	Regular subquery	Correlated subquery
Executes	Once	Once per outer row
References outer query	No	Yes
Typical use	`IN`, scalar comparison	`WHERE col` > `(SELECT ...)`

Correlated EXISTS

-- Patients who have at least one completed appointment
SELECT first_name, last_name
FROM patients p
WHERE EXISTS (
  SELECT 1 FROM appointments a
  WHERE a.patient_id `=` p.id
    AND a.status `=` 'completed'
);

Performance consideration

Correlated subqueries can be slow on large tables — they run N times (once per outer row). For better performance, rewrite as a JOIN with a derived table:

-- Equivalent and usually faster
SELECT e.first_name, e.last_name, e.salary, e.department_id
FROM employees e
JOIN (
  SELECT department_id, AVG(salary) AS dept_avg
  FROM employees
  GROUP BY department_id
) dept ON dept.department_id `=` e.department_id
WHERE e.salary `>` dept.dept_avg;

Or use a window function:

SELECT first_name, last_name, salary, department_id
FROM (
  SELECT *,
    AVG(salary) OVER (PARTITION BY department_id) AS dept_avg
  FROM employees
) t
WHERE salary `>` dept_avg;

Tips

Use correlated subqueries for clarity when tables are small.
If EXPLAIN ANALYZE shows "Seq Scan" on a large table inside a subquery loop, rewrite as a JOIN.

Learning context

This lesson is part of Subqueries & CTEs. Focus on the core idea in Correlated subqueries, then validate with deliberate practice.

What to master

Understand clause order and intent
Write deterministic result queries
Use clear aliases and readable structure

Common mistakes

Skipping ORDER BY in final outputs
Using joins before validating base rows
Not checking row counts after changes

High-level strategy

Start simple, then layer complexity
Run often and compare expected shape
Refactor with CTEs when logic grows

Task ladder

Baseline query: get a correct first output.
Add one complication: edge case, join, filter, or ranking rule.
Re-run and refine until output is stable and explainable.

Transparent data checks

Check row count before and after each major clause.
Validate one manual sample row for correctness.
Confirm final ordering and column naming for trust.

Retention loop

Run the solution once.
Rewrite from memory with one variation.
Explain the logic in plain English in one sentence.

Logical reasoning for commands

WHERE

Why: Limits rows to only the business-relevant subset.

Memory cue: Filter early to reduce noise.

ORDER BY

Why: Makes output deterministic and reviewable.

Memory cue: No ORDER BY means no guaranteed row order.

AS alias

Why: Makes output columns readable for teams and reports.

Memory cue: If the name is clear, the query is easier to trust.

Concept check

Quick check: Correlated subqueries

3 quick questions. One at a time. Instant score at the end.

Exercise 1

Employees earning above their department average

Enterprise Workforce AnalyticseasyPostgreSQL

Return first_name, last_name, salary, and department_id for employees whose salary exceeds the average salary in their own department. Use a correlated subquery. Order by department_id, then salary descending.

Autocomplete: start typing table/column namesPaste supported

No results yet

Run a query to see results here

Exercise 2

Products more expensive than their category average

Commerce Operations IntelligenceeasyPostgreSQL

From the store sandbox, return name, price, and category_id for products whose price exceeds the average price in their own category. Use a correlated subquery. Order by category_id, price descending.

Autocomplete: start typing table/column namesPaste supported

No results yet

Run a query to see results here

Exercise 3

Patients with more appointments than the average patient

Clinical Care PerformanceeasyPostgreSQL

From the hospital sandbox, return first_name and last_name of patients whose appointment count exceeds the average number of appointments per patient. Use a correlated subquery. Order by last_name.

Autocomplete: start typing table/column namesPaste supported

No results yet

Run a query to see results here

Subqueries & CTEs

Lesson 3 / 4

Correlated subqueries

0/3 solved3 to unlock next

PreviousLocked

PrevLocked