0Pricing
SQL Academy · Lesson

Correlated Subqueries

A subquery that depends on the outer row.

Correlated Subqueries is a free SQL Academy lesson on CoddyKit — lesson 1 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Academy learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

What Is a Correlated Subquery?

A correlated subquery is a subquery that references a column from the outer (enclosing) query. Unlike a regular subquery that runs once and returns a fixed result, a correlated subquery is evaluated once for every row processed by the outer query.

This makes them powerful for row-by-row comparisons, but also more expensive than simple subqueries.

Simple vs Correlated Subquery

The key difference: a regular subquery has no reference to the outer query and can stand alone. A correlated subquery depends on the outer row — you can see the outer table alias appearing inside the subquery.

In the example below, the inner SELECT references e1.department_id from the outer query, creating the correlation.

-- Regular subquery (runs once)
SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

-- Correlated subquery (runs once per outer row)
SELECT name, salary, department_id
FROM employees e1
WHERE salary > (
  SELECT AVG(salary)
  FROM employees e2
  WHERE e2.department_id = e1.department_id
);

Setting Up Sample Tables

Let's create two tables we will use throughout this lesson: employees and departments. These give us realistic data to demonstrate correlated subqueries in different scenarios.

CREATE TABLE departments (
  id   INT PRIMARY KEY,
  name VARCHAR(50)
);

CREATE TABLE employees (
  id            INT PRIMARY KEY,
  name          VARCHAR(50),
  department_id INT REFERENCES departments(id),
  salary        NUMERIC(10,2),
  hire_date     DATE
);

INSERT INTO departments VALUES (1,'Engineering'),(2,'Sales'),(3,'HR');

INSERT INTO employees VALUES
  (1,'Alice',  1, 90000, '2020-03-01'),
  (2,'Bob',    1, 75000, '2021-06-15'),
  (3,'Carol',  2, 60000, '2019-01-10'),
  (4,'David',  2, 68000, '2022-09-01'),
  (5,'Eve',    3, 55000, '2020-07-20'),
  (6,'Frank',  1, 95000, '2018-11-05'),
  (7,'Grace',  3, 52000, '2023-02-28'),
  (8,'Henry',  2, 71000, '2021-04-12');

Employees Earning Above Their Department Average

A classic use case for correlated subqueries: find every employee whose salary exceeds the average salary of their own department. The inner query recalculates the department average for each employee row in the outer query.

SELECT e1.name, e1.department_id, e1.salary
FROM employees e1
WHERE e1.salary > (
  SELECT AVG(e2.salary)
  FROM employees e2
  WHERE e2.department_id = e1.department_id
)
ORDER BY e1.department_id, e1.salary DESC;

Using Correlated Subqueries in SELECT

Correlated subqueries are not limited to the WHERE clause — they can also appear in the SELECT list to compute a value for each row. Here we fetch each employee's salary alongside the average salary for their department, all in one query.

SELECT
  e.name,
  e.salary,
  (
    SELECT ROUND(AVG(e2.salary), 2)
    FROM employees e2
    WHERE e2.department_id = e.department_id
  ) AS dept_avg_salary
FROM employees e
ORDER BY e.department_id, e.name;

Finding the Highest-Paid Employee Per Department

We can use a correlated subquery to find the employee with the maximum salary in each department. The inner query finds the max salary for the current row's department, and the outer query keeps only the row that matches it.

SELECT e1.name, e1.department_id, e1.salary
FROM employees e1
WHERE e1.salary = (
  SELECT MAX(e2.salary)
  FROM employees e2
  WHERE e2.department_id = e1.department_id
)
ORDER BY e1.department_id;

EXISTS with a Correlated Subquery

The EXISTS operator is frequently paired with correlated subqueries. It returns TRUE if the inner query produces at least one row. Here we list all departments that have at least one employee hired before 2021.

SELECT d.name AS department
FROM departments d
WHERE EXISTS (
  SELECT 1
  FROM employees e
  WHERE e.department_id = d.id
    AND e.hire_date < '2021-01-01'
);

NOT EXISTS with a Correlated Subquery

NOT EXISTS is the opposite — it returns TRUE when the correlated subquery finds no matching rows. This is useful for finding parent records that have no children, such as departments with no employees.

SELECT d.name AS department_without_employees
FROM departments d
WHERE NOT EXISTS (
  SELECT 1
  FROM employees e
  WHERE e.department_id = d.id
);

Correlated Subquery in UPDATE

Correlated subqueries work inside UPDATE statements too. The following example adds a dept_avg column and then uses a correlated subquery to fill it with each employee's department average salary.

ALTER TABLE employees ADD COLUMN dept_avg NUMERIC(10,2);

UPDATE employees e1
SET dept_avg = (
  SELECT ROUND(AVG(e2.salary), 2)
  FROM employees e2
  WHERE e2.department_id = e1.department_id
);

SELECT name, salary, dept_avg FROM employees ORDER BY department_id, name;

Correlated Subquery in DELETE

You can also use a correlated subquery in a DELETE statement to remove rows based on data from a related table. The query below deletes employees whose salary is below 60% of their department's average — a data-cleanup pattern.

DELETE FROM employees e1
WHERE e1.salary < (
  SELECT AVG(e2.salary) * 0.60
  FROM employees e2
  WHERE e2.department_id = e1.department_id
);

SELECT name, salary, department_id FROM employees ORDER BY department_id;

Performance Tip: Correlated vs JOIN

Correlated subqueries run once per outer row, which can be slow on large tables. Many correlated subqueries can be rewritten as a JOIN with a derived table or CTE for better performance. Understand both patterns and choose based on readability and execution plan.

-- Correlated version (potentially slower)
SELECT e1.name, e1.salary
FROM employees e1
WHERE e1.salary > (
  SELECT AVG(e2.salary)
  FROM employees e2
  WHERE e2.department_id = e1.department_id
);

-- Equivalent JOIN + derived table (often faster)
SELECT e.name, e.salary
FROM employees e
JOIN (
  SELECT department_id, AVG(salary) AS avg_sal
  FROM employees
  GROUP BY department_id
) dept_avg ON dept_avg.department_id = e.department_id
WHERE e.salary > dept_avg.avg_sal;

Quick Check

Test your understanding of correlated subqueries.

Recap: Correlated Subqueries

In this lesson you learned that a correlated subquery references a column from its outer query and is re-evaluated for each outer row. Key takeaways:

  • They can appear in SELECT, WHERE, UPDATE, and DELETE.
  • EXISTS / NOT EXISTS pair naturally with correlated subqueries to test for related rows.
  • They are expressive but can be slow — consider rewriting as a JOIN when performance matters.
  • The outer table alias inside the subquery is what creates the correlation.

Frequently asked questions

Is the “Correlated Subqueries” lesson free?

Yes — the full text of “Correlated Subqueries” is free to read here on the web, and the SQL Academy course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Academy course, upgrade to CoddyKit PRO.

What will I learn in “Correlated Subqueries”?

A subquery that depends on the outer row. You practise SQL Academy with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start SQL Academy?

No prior experience is required. SQL Academy on CoddyKit is structured for beginners through advanced learners; this is — lesson 1 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “Correlated Subqueries” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this SQL Academy lesson?

Yes. Every SQL Academy lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

  1. Correlated Subqueries
  2. EXISTS and NOT EXISTS
  3. IN vs ANY vs ALL
  4. EXISTS vs JOIN Performance
← Back to SQL Academy