Skip to content
elephantoo

Subqueries & correlated subqueries

Lesson 14 of 31 17 min read

Scalar, list and table subqueries, IN, EXISTS, ANY/ALL, derived tables and correlated subqueries.

A subquery is a SELECT inside another statement, wrapped in parentheses. Subqueries let you answer questions in steps: "employees who earn more than the average", "products that have never been ordered". MySQL 8 optimises most of them well. Many can be rewritten as joins, and you'll learn when each style fits.

Sample data#

SQL
CREATE TABLE departments (id INT PRIMARY KEY, name VARCHAR(20) NOT NULL);
CREATE TABLE employees (
    id      INT PRIMARY KEY,
    name    VARCHAR(20) NOT NULL,
    dept_id INT,
    salary  INT NOT NULL,
    FOREIGN KEY (dept_id) REFERENCES departments (id)
);

INSERT INTO departments VALUES (1, 'Engineering'), (2, 'Sales'), (3, 'Support'), (4, 'Legal');
INSERT INTO employees VALUES
    (1, 'Asha',   1, 95000), (2, 'Rohan', 1, 72000), (3, 'Neha',  1, 81000),
    (4, 'Meera',  2, 58000), (5, 'Karan', 2, 61000), (6, 'Vikram', 2, 88000),
    (7, 'Zoya',   3, 45000), (8, 'Imran', NULL, 50000);

Scalar subqueries: one value#

A subquery that returns a single row and column can be used like a value:

SQL
SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees)
ORDER BY salary DESC;
Output
+--------+--------+
| name   | salary |
+--------+--------+
| Asha   |  95000 |
| Vikram |  88000 |
| Neha   |  81000 |
| Rohan  |  72000 |
+--------+--------+

The inner query runs first (average 68,750), then the outer query compares against it. A scalar subquery can also appear in the SELECT list:

SQL
SELECT name, salary,
       salary - (SELECT AVG(salary) FROM employees) AS vs_avg
FROM employees
WHERE dept_id = 2;
Output
+--------+--------+-------------+
| name   | salary | vs_avg      |
+--------+--------+-------------+
| Meera  |  58000 | -10750.0000 |
| Karan  |  61000 |  -7750.0000 |
| Vikram |  88000 |  19250.0000 |
+--------+--------+-------------+

If a "scalar" subquery returns more than one row, MySQL raises ERROR 1242: Subquery returns more than 1 row.

IN and NOT IN: a list of values#

SQL
-- Departments that have at least one employee earning over 80k
SELECT name FROM departments
WHERE id IN (SELECT dept_id FROM employees WHERE salary > 80000);
Output
+-------------+
| name        |
+-------------+
| Engineering |
| Sales       |
+-------------+

The NOT IN trap. Imran's dept_id is NULL, so the subquery below returns a NULL along with the real ids, and the result is empty:

SQL
SELECT name FROM departments
WHERE id NOT IN (SELECT dept_id FROM employees);
Output
Empty set (0.00 sec)

4 NOT IN (1, 2, 3, NULL) means 4 <> 1 AND 4 <> 2 AND 4 <> 3 AND 4 <> NULL, and the last part is unknown, so the whole condition is never true. Fix it with WHERE dept_id IS NOT NULL inside the subquery, or, better, use NOT EXISTS.

EXISTS and NOT EXISTS#

EXISTS (subquery) is true if the subquery returns at least one row. What it selects doesn't matter (SELECT 1 is conventional):

SQL
SELECT d.name
FROM departments d
WHERE NOT EXISTS (SELECT 1 FROM employees e WHERE e.dept_id = d.id);
Output
+-------+
| name  |
+-------+
| Legal |
+-------+

Legal has no employees. NOT EXISTS handles NULLs correctly and is the safest way to write an anti-join. Notice that the subquery mentions d.id from the outer query. That makes it a correlated subquery.

Correlated subqueries#

A correlated subquery refers to the current row of the outer query, so logically it runs once per outer row. Who earns more than the average of their own department?

SQL
SELECT e.name, e.dept_id, e.salary
FROM employees e
WHERE e.salary > (
    SELECT AVG(e2.salary)
    FROM employees e2
    WHERE e2.dept_id = e.dept_id      -- correlation
)
ORDER BY e.dept_id;
Output
+--------+---------+--------+
| name   | dept_id | salary |
+--------+---------+--------+
| Asha   |       1 |  95000 |
| Vikram |       2 |  88000 |
+--------+---------+--------+

For Asha, the inner query averages department 1 (82,667), and for Vikram it averages department 2 (69,000). Correlated subqueries in the SELECT list are a readable way to add per-row lookups:

SQL
SELECT d.name,
       (SELECT COUNT(*)    FROM employees e WHERE e.dept_id = d.id) AS headcount,
       (SELECT MAX(salary) FROM employees e WHERE e.dept_id = d.id) AS top_salary
FROM departments d;
Output
+-------------+-----------+------------+
| name        | headcount | top_salary |
+-------------+-----------+------------+
| Engineering |         3 |      95000 |
| Sales       |         3 |      88000 |
| Support     |         1 |      45000 |
| Legal       |         0 |       NULL |
+-------------+-----------+------------+

On big tables, correlated subqueries can be slow if MySQL really does run them per row. Check with EXPLAIN (covered later) and consider a join or window function instead.

ANY and ALL#

> ALL (subquery) means greater than every value, and > ANY (subquery) means greater than at least one:

SQL
SELECT name, salary FROM employees
WHERE salary > ALL (SELECT salary FROM employees WHERE dept_id = 2);
Output
+------+--------+
| name | salary |
+------+--------+
| Asha |  95000 |
+------+--------+

= ANY (...) is the same as IN (...). In practice, > (SELECT MAX(...)) is often clearer than > ALL.

Derived tables: subqueries in FROM#

A subquery in FROM produces a temporary result set, called a derived table, which must have an alias:

SQL
SELECT d.name, t.avg_salary
FROM (
    SELECT dept_id, ROUND(AVG(salary)) AS avg_salary
    FROM employees
    WHERE dept_id IS NOT NULL
    GROUP BY dept_id
) AS t
JOIN departments d ON d.id = t.dept_id
WHERE t.avg_salary > 60000;
Output
+-------------+------------+
| name        | avg_salary |
+-------------+------------+
| Engineering |      82667 |
| Sales       |      69000 |
+-------------+------------+

Derived tables are perfect for "aggregate first, then join or filter". MySQL 8.0.14+ also supports LATERAL derived tables, which can refer to earlier tables in the same FROM clause. For readability, the next-but-one lesson's CTEs (WITH ...) are usually nicer than deeply nested derived tables.

Row subqueries#

You can compare several columns at once:

SQL
SELECT name FROM employees
WHERE (dept_id, salary) = (SELECT dept_id, MAX(salary) FROM employees WHERE dept_id = 1 GROUP BY dept_id);
Output
+------+
| name |
+------+
| Asha |
+------+

Subqueries in UPDATE and DELETE#

SQL
-- 5% raise for everyone in departments with fewer than 3 people
UPDATE employees
SET salary = salary * 1.05
WHERE dept_id IN (
    SELECT id FROM (
        SELECT d.id FROM departments d
        JOIN employees e ON e.dept_id = d.id
        GROUP BY d.id HAVING COUNT(*) < 3
    ) AS small_depts
);
SELECT name, salary FROM employees WHERE dept_id = 3;
Output
+------+--------+
| name | salary |
+------+--------+
| Zoya |  47250 |
+------+--------+

MySQL doesn't let you modify a table and select from the same table in a plain subquery (ERROR 1093). Wrapping the subquery in an extra derived table, as above, materialises it first and works around the restriction.

Subquery or join?#

  • Use EXISTS / NOT EXISTS for "has / has no matching rows". It's clear, NULL-safe and never duplicates rows.
  • Use a join when you need columns from both tables.
  • Use a derived table or CTE to aggregate before joining.
  • MySQL's optimiser often turns IN subqueries into semi-joins automatically, so write the version that reads most clearly first, then check performance with EXPLAIN.

What's next#

Next you'll stack result sets on top of each other with UNION, and find overlaps and differences with INTERSECT and EXCEPT.

Check your understanding

Quick quiz

0/3 answered

  1. 1.What makes a subquery *correlated*?

  2. 2.Why can WHERE id NOT IN (SELECT manager_id FROM staff) return no rows at all?

  3. 3.What must you add to a subquery used in the FROM clause (a derived table)?

Finished reading?

Mark this lesson complete to track your progress.