Correlated subqueries in MySQL

Introduction

Correlated subqueries let you compare each row in one table with rows in another table. They help find related data by checking conditions row by row.

When you want to find rows in one table that depend on values in another table for each row.

When you need to compare each employee's salary to the average salary in their department.

When you want to find customers who placed orders larger than their average order amount.

When you want to filter products that cost more than the average price in their category.

Syntax

MySQL

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

The subquery uses a column from the outer query to compare values row by row.

The subquery runs once for each row in the outer query, so it can be slower on large data.

Examples

Find employees who earn more than the average salary in their own department.

MySQL

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

Find customers who have at least one order with total greater than 100.

MySQL

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.total > 100
);

Sample Program

This query finds employees who earn more than the average salary in their department.

MySQL

CREATE TABLE departments (
  department_id INT PRIMARY KEY,
  department_name VARCHAR(50)
);

CREATE TABLE employees (
  employee_id INT PRIMARY KEY,
  name VARCHAR(50),
  salary DECIMAL(10,2),
  department_id INT,
  FOREIGN KEY (department_id) REFERENCES departments(department_id)
);

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

INSERT INTO employees VALUES
(101, 'Alice', 70000, 1),
(102, 'Bob', 60000, 1),
(103, 'Charlie', 80000, 2),
(104, 'Diana', 90000, 2);

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

Important Notes

Correlated subqueries can be slower than joins because the subquery runs for each row in the outer query.

Use correlated subqueries when you need row-by-row comparison that joins cannot easily do.

Make sure the subquery references the outer query table with an alias to avoid confusion.

Summary

Correlated subqueries compare each row in one table with related rows in another.

They run the subquery once per outer row, so they can be slower on big data.

Useful for finding rows based on related data conditions that depend on each row.