Correlated subquery execution model in SQL

Introduction

A correlated subquery helps find related data by using values from the main query. It runs once for each row in the main query to give precise matching results.

When you want to compare each row in a table with related rows in another table.

When you need to filter rows based on a condition that depends on each row's data.

When you want to calculate a value for each row using data from another table.

When you want to check if a related record exists for each row in the main query.

Syntax

SQL

SELECT column1, column2
FROM table1
WHERE column3 operator (
  SELECT columnX
  FROM table2
  WHERE table2.columnY = table1.columnZ
);

The subquery uses a column from the outer query to filter its results.

The subquery runs once for each row of the outer query, making it 'correlated'.

Examples

Find employees whose salary is higher than the average salary in their own department.

SQL

SELECT e.name
FROM employees e
WHERE e.salary > (
  SELECT AVG(salary)
  FROM employees
  WHERE department = e.department
);

Find customers who have placed orders after January 1, 2024.

SQL

SELECT c.customer_id, c.name
FROM customers c
WHERE EXISTS (
  SELECT 1
  FROM orders o
  WHERE o.customer_id = c.customer_id
  AND o.order_date > '2024-01-01'
);

Sample Program

This query finds employees who earn more than the average salary in their department using a correlated subquery.

SQL

CREATE TABLE employees (
  id INT,
  name VARCHAR(50),
  department VARCHAR(50),
  salary INT
);

INSERT INTO employees VALUES
(1, 'Alice', 'Sales', 5000),
(2, 'Bob', 'Sales', 4000),
(3, 'Charlie', 'HR', 4500),
(4, 'Diana', 'HR', 5500);

SELECT name, salary
FROM employees e
WHERE salary > (
  SELECT AVG(salary)
  FROM employees
  WHERE department = e.department
);

Important Notes

Correlated subqueries can be slower because they run once per outer row.

Use indexes on columns used in the correlation to improve performance.

Sometimes, you can rewrite correlated subqueries as joins for better speed.

Summary

A correlated subquery uses data from the outer query to filter results.

It runs once for each row in the outer query, making it dynamic and precise.

Useful for comparing or checking related data row by row.