#1 India's Top IT Training Institute
×

About Uncodemy

Know more about who we are and what we stand for.

About Us

About Us

Discover our mission to equip individuals with essential IT skills and industry expertise.

Read More →
UnCodeMy Gallery

UnCodeMy Gallery

Explore highlights from our IT training sessions and success stories.

Read More →
Mentors

Mentors

Meet the industry experts who train and guide you through your course.

Read More →
Free Resources

Free Resources

Free tutorials, interview questions, tools and calculators to help you learn.

Explore →
×

Free Resources

Learn, practice and plan your career — bilkul free.

Tutorials

Tutorials

Step-by-step written tutorials to learn any technology at your own pace.

Read More →
Free Counselling

Free Counselling

Book a free video counselling session and pick the right course for you.

Read More →
Online Compiler

Online Compiler

Write, run and test your code online — no setup required.

Read More →
MySQL Tutorial · Data Science

Correlated Subqueries in MySQL Explained

"Find the employees who earn more than the average salary of their own department." A plain subquery cannot answer this, because there is no single average to compare against: Sales has one average, IT another. The inner query has to know which department the outer row belongs to. When a subquery refers to a column of the outer query like this, it is a correlated subquery, and logically it is evaluated once for every row of the outer query.

Sample data

CREATE TABLE employees (emp_id INT PRIMARY KEY, name VARCHAR(20), dept VARCHAR(10), salary INT);
INSERT INTO employees VALUES
 (1,'Anjali','Sales',52000), (2,'Bilal','Sales',61000), (3,'Charu','Sales',45000),
 (4,'Deepak','IT',90000),    (5,'Esha','IT',78000),     (6,'Faisal','HR',50000);

CREATE TABLE leave_requests (req_id INT PRIMARY KEY, emp_id INT, leave_date DATE);
INSERT INTO leave_requests VALUES
 (1,1,'2026-11-03'), (2,1,'2026-11-10'), (3,4,'2026-11-07'), (4,6,'2026-11-12');

Department averages for reference: Sales 52,666.67, IT 84,000, HR 50,000.

Example 1: compare each row with its own group

SELECT e.name, e.dept, e.salary
FROM employees e
WHERE e.salary > (
    SELECT AVG(e2.salary)
    FROM employees e2
    WHERE e2.dept = e.dept        -- e.dept comes from the OUTER row
)
ORDER BY e.emp_id;
namedeptsalary
BilalSales61000
DeepakIT90000

Dry run, row by row

Outer rowInner query becomesInner resultsalary > result?
Anjali, Sales, 52000AVG where dept = 'Sales'52666.67No
Bilal, Sales, 61000AVG where dept = 'Sales'52666.67Yes
Charu, Sales, 45000AVG where dept = 'Sales'52666.67No
Deepak, IT, 90000AVG where dept = 'IT'84000Yes
Esha, IT, 78000AVG where dept = 'IT'84000No
Faisal, HR, 50000AVG where dept = 'HR'50000No (equal, not greater)

The two aliases matter: e is the outer copy of the table and e2 the inner copy. Without them, WHERE dept = dept would compare the inner row with itself and always be true.

Example 2: EXISTS and NOT EXISTS

The most common correlated subqueries in real code check whether related rows exist. Who has not applied for any leave this month?

SELECT e.name
FROM employees e
WHERE NOT EXISTS (
    SELECT 1 FROM leave_requests l WHERE l.emp_id = e.emp_id
)
ORDER BY e.emp_id;
name
Bilal
Charu
Esha

EXISTS only cares whether at least one row comes back, so SELECT 1 is the usual convention; the selected value is ignored.

Example 3: a correlated subquery in the SELECT list

SELECT e.name,
       (SELECT MAX(l.leave_date) FROM leave_requests l WHERE l.emp_id = e.emp_id) AS last_leave
FROM employees e
ORDER BY e.emp_id;
namelast_leave
Anjali2026-11-10
BilalNULL
CharuNULL
Deepak2026-11-07
EshaNULL
Faisal2026-11-12

A scalar subquery in the select list must return one value per outer row; if it could return two rows MySQL raises error 1242 "Subquery returns more than 1 row". An aggregate such as MAX() always returns exactly one value.

The NOT IN trap that NOT EXISTS avoids

Suppose one leave request was saved without an employee ID:

INSERT INTO leave_requests VALUES (5, NULL, '2026-11-15');

SELECT name FROM employees
WHERE emp_id NOT IN (SELECT emp_id FROM leave_requests);

Result: Empty set. Because the list now contains a NULL, emp_id NOT IN (1, 1, 4, 6, NULL) can never be true; it is either false or unknown. The NOT EXISTS query from Example 2 still returns Bilal, Charu and Esha, because it compares row by row and simply finds no match for them. Prefer NOT EXISTS whenever the inner column can be NULL. This behaviour is explained further in handling NULL values in MySQL.

Performance, and two rewrites

EXPLAIN on Example 1 shows the inner query as a DEPENDENT SUBQUERY, the plan label for "depends on the outer row":

idselect_typetabletyperows
1PRIMARYeALL6
2DEPENDENT SUBQUERYe2ALL6

On six rows this costs nothing. On 2 lakh employees, recomputing a department average for every row is slow unless dept is indexed, and even then a single-pass rewrite is usually better. MySQL 8 often transforms EXISTS and IN subqueries into semi-joins automatically, but a correlated scalar subquery like Example 1 is a good candidate to rewrite by hand. Both versions below return the same two rows (Bilal and Deepak):

-- Rewrite A: join to a pre-aggregated derived table (works on any version)
SELECT e.name, e.dept, e.salary
FROM employees e
JOIN (SELECT dept, AVG(salary) AS avg_sal FROM employees GROUP BY dept) d
  ON d.dept = e.dept
WHERE e.salary > d.avg_sal
ORDER BY e.emp_id;

-- Rewrite B: window function (MySQL 8.0+)
SELECT name, dept, salary
FROM (SELECT emp_id, name, dept, salary,
             AVG(salary) OVER (PARTITION BY dept) AS avg_sal
      FROM employees) t
WHERE salary > avg_sal
ORDER BY emp_id;

Each computes the averages once instead of once per row. Measure with EXPLAIN ANALYZE on your own data before deciding; for small tables or highly selective indexed lookups, the correlated version can be perfectly fine and easier to read.

Common mistakes

Start with the basics in the MySQL subqueries tutorial, compare approaches in JOIN vs subquery performance, and learn the window-function rewrite in the window functions tutorial.

Launch Your Data Science Career

Learn MySQL, Python, Machine Learning and AI with Uncodemy's Data Science Training in Noida — hands-on projects, industry mentors and Dedicated Placement Support.

Enroll in Data Science Course →