Chapter 23 of 35

Correlated Subqueries & EXISTS

In the Subqueries chapter, every inner query was self-contained: (SELECT AVG(salary) FROM employees) gives the same answer no matter which outer row we are looking at, so it logically runs once. Now consider this question: "Which employees earn more than the average salary of their own department?"

Aarav must be compared with the Engineering average, Sneha with the Sales average, Karan with the HR average. The inner query has to be different for each outer row. That is a correlated subquery. In this chapter we will see how correlated subqueries work row by row, then meet their most important use: the EXISTS and NOT EXISTS operators, which answer "is there at least one matching row?" and are the safest way to write "find rows with no match".

All examples use the shared company_db sample database, and every output shown is real MySQL 8.4 output.


What is a Correlated Subquery?

A correlated subquery is a subquery that refers to a column of the outer query. Because its result depends on the current outer row, it is logically evaluated once for every row processed by the outer query.

SELECT e1.name, e1.dept_id, e1.salary
FROM employees e1
WHERE e1.salary > (SELECT AVG(e2.salary)
                   FROM employees e2
                   WHERE e2.dept_id = e1.dept_id);
+--------------+---------+-----------+
| name         | dept_id | salary    |
+--------------+---------+-----------+
| Aarav Sharma |      10 | 150000.00 |
| Sneha Kapoor |      20 | 110000.00 |
| Karan Patel  |      30 |  70000.00 |
+--------------+---------+-----------+
3 rows in set (0.00 sec)

The key is e2.dept_id = e1.dept_id. The alias e1 belongs to the outer query, so the inner query cannot run on its own: try running just SELECT AVG(e2.salary) FROM employees e2 WHERE e2.dept_id = e1.dept_id and MySQL will say Unknown column 'e1.dept_id'. That link to the outer row is the "correlation".

Both queries read the same table, so the aliases e1 and e2 are essential. They tell SQL which copy of employees each column belongs to.


How It Executes: A Row-by-Row Trace

Conceptually, MySQL does this:

for each row r in employees (as e1):
    v = SELECT AVG(salary) FROM employees WHERE dept_id = r.dept_id
    if r.salary > v: output r

Let us trace all 10 rows:

Outer row (e1)

dept_id

salary

Inner query computes

salary > avg?

Aarav Sharma

10

150000

AVG of dept 10 = 113333.33

TRUE

Priya Iyer

10

95000

AVG of dept 10 = 113333.33

FALSE

Rohan Mehta

10

95000

AVG of dept 10 = 113333.33

FALSE

Sneha Kapoor

20

110000

AVG of dept 20 = 76666.67

TRUE

Vikram Singh

20

62000

AVG of dept 20 = 76666.67

FALSE

Ananya Das

20

58000

AVG of dept 20 = 76666.67

FALSE

Karan Patel

30

70000

AVG of dept 30 = 59000

TRUE

Meera Nair

30

48000

AVG of dept 30 = 59000

FALSE

Arjun Reddy

40

82000

AVG of dept 40 = 82000

FALSE (equal, not greater)

Ishita Gupta

NULL

55000

dept_id = NULL matches nothing, AVG = NULL

UNKNOWN

Two things to notice:

  • Arjun is the only employee in Marketing, so he is the average. He is not "more than" it.

  • Ishita has no department. Inside the subquery, e2.dept_id = NULL is never TRUE, so the subquery averages zero rows and returns NULL. 55000 > NULL is UNKNOWN, so she is dropped.

You can see each department average next to every employee by putting the correlated subquery in the SELECT list:

SELECT e.name, e.dept_id, e.salary,
       (SELECT AVG(x.salary) FROM employees x
        WHERE x.dept_id = e.dept_id) AS dept_avg
FROM employees e
ORDER BY e.dept_id, e.emp_id;
+--------------+---------+-----------+---------------+
| name         | dept_id | salary    | dept_avg      |
+--------------+---------+-----------+---------------+
| Ishita Gupta |    NULL |  55000.00 |          NULL |
| Aarav Sharma |      10 | 150000.00 | 113333.333333 |
| Priya Iyer   |      10 |  95000.00 | 113333.333333 |
| Rohan Mehta  |      10 |  95000.00 | 113333.333333 |
| Sneha Kapoor |      20 | 110000.00 |  76666.666667 |
| Vikram Singh |      20 |  62000.00 |  76666.666667 |
| Ananya Das   |      20 |  58000.00 |  76666.666667 |
| Karan Patel  |      30 |  70000.00 |  59000.000000 |
| Meera Nair   |      30 |  48000.00 |  59000.000000 |
| Arjun Reddy  |      40 |  82000.00 |  82000.000000 |
+--------------+---------+-----------+---------------+
10 rows in set (0.00 sec)

Correlated vs non-correlated

Non-correlated subquery

Correlated subquery

Refers to outer query?

No

Yes

Can run on its own?

Yes

No

Logical evaluation

Once

Once per outer row

Result

Same for every outer row

Can differ for each outer row

Typical use

Compare with a global value

Compare with a per-group / per-row value, EXISTS

"Once per outer row" describes the meaning, not necessarily what the engine physically does. The optimizer is free to compute the same answer more cleverly, as we will see in the performance section.


Correlated Subqueries in the SELECT List

A correlated scalar subquery in SELECT is a neat way to attach a per-row summary. Each customer with their order count and total:

SELECT c.name,
       (SELECT COUNT(*) FROM orders o
        WHERE o.customer_id = c.customer_id)   AS num_orders,
       (SELECT SUM(o.amount) FROM orders o
        WHERE o.customer_id = c.customer_id)   AS total_spent
FROM customers c;
+---------------+------------+-------------+
| name          | num_orders | total_spent |
+---------------+------------+-------------+
| Rahul Verma   |          3 |     9300.00 |
| Kavya Menon   |          2 |     7900.00 |
| Aditya Joshi  |          1 |     7300.00 |
| Neha Agarwal  |          1 |      950.00 |
| Siddharth Rao |          0 |        NULL |
+---------------+------------+-------------+
5 rows in set (0.00 sec)

Siddharth has no orders: COUNT(*) of zero rows is 0, but SUM of zero rows is NULL. Wrap it as COALESCE(SUM(o.amount), 0) if you want 0. Unlike an inner join, this keeps customers with no orders automatically, similar to a LEFT JOIN + GROUP BY.

Latest order of each customer

A very common pattern: keep a row only if it is the maximum within its group.

SELECT o.customer_id, o.order_id, o.order_date, o.amount
FROM orders o
WHERE o.order_date = (SELECT MAX(o2.order_date)
                      FROM orders o2
                      WHERE o2.customer_id = o.customer_id)
ORDER BY o.customer_id;
+-------------+----------+------------+---------+
| customer_id | order_id | order_date | amount  |
+-------------+----------+------------+---------+
|         101 |     1007 | 2024-03-28 | 5600.00 |
|         102 |     1006 | 2024-03-11 | 3100.00 |
|         103 |     1004 | 2024-02-14 | 7300.00 |
|         104 |     1005 | 2024-03-01 |  950.00 |
+-------------+----------+------------+---------+
4 rows in set (0.00 sec)

The guest order 1008 (customer NULL) is missing because o2.customer_id = NULL matches nothing. If a customer had two orders on the same latest date, both would be returned.


Nth Highest Salary with a Correlated Subquery

The classic interview trick: a salary is the Nth highest if exactly N − 1 distinct salaries are greater than it. For the 3rd highest, N − 1 = 2:

SELECT DISTINCT e1.salary
FROM employees e1
WHERE 2 = (SELECT COUNT(DISTINCT e2.salary)
           FROM employees e2
           WHERE e2.salary > e1.salary);
+----------+
| salary   |
+----------+
| 95000.00 |
+----------+
1 row in set (0.00 sec)
Distinct salaries (desc): 150000, 110000, 95000, 82000, 70000, 62000, 58000, 55000, 48000

e1.salary   distinct salaries greater   count   = 2 ?
150000      (none)                        0      no
110000      150000                        1      no
 95000      150000, 110000                2      YES  -> 3rd highest
 82000      150000, 110000, 95000         3      no
 ...

It works on any SQL version, but it runs a counting subquery for every row (roughly O(n²) work). With window functions, DENSE_RANK() does it in one pass; see the Ranking Functions chapter.


The EXISTS Operator

EXISTS (subquery) is TRUE if the subquery returns at least one row, and FALSE if it returns no rows. It never returns UNKNOWN.

EXISTS only cares whether rows come back, never what they contain. Customers who have placed at least one order:

SELECT c.name
FROM customers c
WHERE EXISTS (SELECT 1
              FROM orders o
              WHERE o.customer_id = c.customer_id);
+--------------+
| name         |
+--------------+
| Rahul Verma  |
| Kavya Menon  |
| Aditya Joshi |
| Neha Agarwal |
+--------------+
4 rows in set (0.00 sec)
Outer row               Inner: orders WHERE customer_id = ?     EXISTS?
101 Rahul Verma         1001, 1003, 1007                        TRUE
102 Kavya Menon         1002, 1006                              TRUE
103 Aditya Joshi        1004                                    TRUE
104 Neha Agarwal        1005                                    TRUE
105 Siddharth Rao       (no rows)                               FALSE

Each customer appears once even though Rahul has three orders. Like IN in the previous chapter, EXISTS is a semi-join. And because EXISTS only needs to know whether there is one row, the engine can stop scanning as soon as it finds the first match.

SELECT 1, SELECT *, SELECT NULL: does it matter?

No. The select list inside EXISTS is ignored. SELECT 1 is a popular convention because it makes clear that no data is needed. Even SELECT NULL counts as "a row exists":

SELECT dept_name FROM departments WHERE EXISTS (SELECT NULL);
+-------------+
| dept_name   |
+-------------+
| Engineering |
| HR          |
| Marketing   |
| Research    |
| Sales       |
+-------------+
5 rows in set (0.00 sec)

SELECT NULL returns one row (containing NULL), so EXISTS is TRUE for every department. A subquery that returns no rows makes it FALSE:

SELECT dept_name FROM departments
WHERE EXISTS (SELECT 1 FROM employees WHERE salary > 1000000);
Empty set (0.00 sec)

These two examples are not correlated, so EXISTS gives the same answer for every row: all or nothing. That is almost never what you want. A useful EXISTS nearly always contains a condition linking the inner table to the outer row.

EXISTS with extra conditions

You can put any filter in the inner WHERE. Departments that have at least one employee earning over 1 lakh:

SELECT d.dept_name
FROM departments d
WHERE EXISTS (SELECT 1 FROM employees e
              WHERE e.dept_id = d.dept_id
                AND e.salary > 100000);
+-------------+
| dept_name   |
+-------------+
| Engineering |
| Sales       |
+-------------+
2 rows in set (0.00 sec)

The equivalent WHERE d.dept_id IN (SELECT e.dept_id FROM employees e WHERE e.salary > 100000) returns the same 2 rows. For positive checks, IN and EXISTS are interchangeable in results (and MySQL 8 optimises both as semi-joins). The difference appears with the negative forms.

Another example, which employees are managers (someone reports to them)?

SELECT m.emp_id, m.name
FROM employees m
WHERE EXISTS (SELECT 1 FROM employees r
              WHERE r.manager_id = m.emp_id);
+--------+--------------+
| emp_id | name         |
+--------+--------------+
|      1 | Aarav Sharma |
|      4 | Sneha Kapoor |
|      7 | Karan Patel  |
+--------+--------------+
3 rows in set (0.00 sec)

Compare this with the SELF JOIN chapter: a self join would list Aarav once per report (6 times). EXISTS lists each manager once.


NOT EXISTS: Finding Rows With No Match

NOT EXISTS keeps outer rows for which the subquery returns nothing. This is called an anti-join. Departments with no employees:

SELECT d.dept_id, d.dept_name
FROM departments d
WHERE NOT EXISTS (SELECT 1 FROM employees e
                  WHERE e.dept_id = d.dept_id);
+---------+-----------+
| dept_id | dept_name |
+---------+-----------+
|      50 | Research  |
+---------+-----------+
1 row in set (0.00 sec)

Remember that in the Subqueries chapter, dept_id NOT IN (SELECT dept_id FROM employees) returned an empty set, because Ishita's NULL dept_id poisoned the list. NOT EXISTS gets it right with no extra filter.

NOT IN vs NOT EXISTS side by side

Customers who have never ordered. The orders table contains a guest order with customer_id = NULL:

SELECT customer_id, name
FROM customers
WHERE customer_id NOT IN (SELECT customer_id FROM orders);
Empty set (0.00 sec)
SELECT c.customer_id, c.name
FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o
                  WHERE o.customer_id = c.customer_id);
+-------------+---------------+
| customer_id | name          |
+-------------+---------------+
|         105 | Siddharth Rao |
+-------------+---------------+
1 row in set (0.00 sec)

Why the difference? Look at Siddharth (105) under both:

NOT IN:      105 NOT IN (101, 102, 101, 103, 104, 102, 101, NULL)
           = 105<>101 AND ... AND 105<>NULL
           = TRUE AND ... AND UNKNOWN  = UNKNOWN      -> dropped

NOT EXISTS:  rows of orders WHERE customer_id = 105  -> none
             (the NULL row fails "NULL = 105", so it is simply not a match)
             EXISTS = FALSE, NOT EXISTS = TRUE          -> kept

EXISTS works with two-valued logic: a subquery either returns rows or it does not. A NULL inside the subquery just means "this row did not match", it can never turn the whole answer into UNKNOWN.

IN

EXISTS

NOT IN

NOT EXISTS

Can return UNKNOWN

Yes

No

Yes

No

Safe with NULLs in subquery

Yes (same result)

Yes

No, can return empty

Yes

Usually correlated

No

Yes

No

Yes

Join type

Semi-join

Semi-join

Anti-join

Anti-join

Rule of thumb: for "rows with no match", reach for NOT EXISTS (or LEFT JOIN ... WHERE right.key IS NULL) instead of NOT IN.


Double NOT EXISTS: "For All" Queries (Relational Division)

SQL has no "for all" operator, but you can express it with two negations: "x matches all items" is the same as "there is no item that x does not match". This is the SQL form of relational division from relational algebra, and it appears in GATE and university exams.

Which customers placed an order in every month of January, February and March 2024? We build a tiny list of months with UNION ALL (from the UNION & Set Operations chapter) and use MONTH(date), which returns the month number (Date & Time Functions chapter):

SELECT c.name
FROM customers c
WHERE NOT EXISTS (                       -- there is no month ...
    SELECT 1
    FROM (SELECT 1 AS m UNION ALL SELECT 2 UNION ALL SELECT 3) AS months
    WHERE NOT EXISTS (                   -- ... in which c has no order
        SELECT 1 FROM orders o
        WHERE o.customer_id = c.customer_id
          AND MONTH(o.order_date) = months.m));
+-------------+
| name        |
+-------------+
| Rahul Verma |
+-------------+
1 row in set (0.00 sec)
Customer   Jan  Feb  Mar   a month with no order?   result
101        yes  yes  yes   none                     KEEP
102        yes  -    yes   Feb                      drop
103        -    yes  -     Jan, Mar                 drop
104        -    -    yes   Jan, Feb                 drop
105        -    -    -     Jan, Feb, Mar            drop

Read it from the inside out: for a customer and a month, the innermost query looks for an order. The middle NOT EXISTS keeps the months with no order. The outer NOT EXISTS keeps the customer only if that list of "missing months" is empty.


Correlated UPDATE and DELETE

Correlated subqueries are very useful for changing data based on related rows. These demos were run on a copy of the sample database.

UPDATE: store each customer's total spend

ALTER TABLE customers ADD COLUMN total_spent DECIMAL(10,2) NOT NULL DEFAULT 0;

UPDATE customers c
SET c.total_spent = (SELECT COALESCE(SUM(o.amount), 0)
                     FROM orders o
                     WHERE o.customer_id = c.customer_id
                       AND o.status <> 'Cancelled');
Query OK, 4 rows affected (0.00 sec)
Rows matched: 5  Changed: 4  Warnings: 0
SELECT customer_id, name, total_spent FROM customers;
+-------------+---------------+-------------+
| customer_id | name          | total_spent |
+-------------+---------------+-------------+
|         101 | Rahul Verma   |     8100.00 |
|         102 | Kavya Menon   |     7900.00 |
|         103 | Aditya Joshi  |     7300.00 |
|         104 | Neha Agarwal  |      950.00 |
|         105 | Siddharth Rao |        0.00 |
+-------------+---------------+-------------+
5 rows in set (0.00 sec)

Rahul's cancelled order of 1,200 is excluded (9,300 − 1,200 = 8,100). All 5 rows matched, but only 4 changed: Siddharth's value was already 0. The COALESCE matters here: without it, Siddharth would get SUM of no rows = NULL, and the NOT NULL column would reject it.

DELETE: remove customers who never ordered

DELETE FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o
                  WHERE o.customer_id = c.customer_id);
Query OK, 1 row affected (0.00 sec)

Siddharth Rao was deleted. (A table alias in a single-table DELETE like DELETE FROM customers c is allowed from MySQL 8.0.16.)

The same-table restriction still applies

Give a 10% raise to everyone who earns less than their department average:

UPDATE employees e
SET e.salary = e.salary * 1.10
WHERE e.salary < (SELECT AVG(x.salary) FROM employees x
                  WHERE x.dept_id = e.dept_id);
ERROR 1093 (HY000): You can't specify target table 'e' for update in FROM clause

This is the MySQL error 1093 from the Subqueries chapter: the subquery reads the table being updated. Here it would also be logically dangerous: after Priya gets her raise, the Engineering average changes, and whether Rohan qualifies would depend on the order rows are processed. The standard fix is to compute the averages first, in a derived table, and join to it: UPDATE employees e JOIN (SELECT dept_id, AVG(salary) AS a FROM employees GROUP BY dept_id) d ON d.dept_id = e.dept_id SET e.salary = e.salary * 1.10 WHERE e.salary < d.a;


A Dangerous Bug: Accidental Correlation

Back on the original sample database (all 5 customers present), look at this query carefully. Does it look like it should fail?

SELECT name FROM customers
WHERE customer_id IN (SELECT customer_id FROM departments);
+---------------+
| name          |
+---------------+
| Rahul Verma   |
| Kavya Menon   |
| Aditya Joshi  |
| Neha Agarwal  |
| Siddharth Rao |
+---------------+
5 rows in set (0.00 sec)

The departments table has no customer_id column, yet there is no error, and every customer is returned. Why? SQL resolves an unqualified column name by looking first in the subquery's own tables, and if it is not found there, in the outer query's tables. So customer_id silently became customers.customer_id, the subquery turned into a correlated one, and the condition became "customer_id IN (customer_id, customer_id, ...)", which is always true.

Imagine that inside a DELETE: DELETE FROM customers WHERE customer_id IN (SELECT customer_id FROM departments) would target every customer. (In our sample database the foreign key from orders happens to block it with error 1451, but a table without such protection would simply be emptied.) The defence is simple: always qualify columns with table aliases in subqueries. Written as SELECT d.customer_id FROM departments d, the mistake becomes an immediate error:

ERROR 1054 (42S22): Unknown column 'd.customer_id' in 'field list'

Performance and Alternatives

"Evaluated once per outer row" sounds slow, and on big tables a naive correlated subquery can be: 1 lakh outer rows means 1 lakh inner queries. Three things help:

  1. Indexes on the correlation column. The inner query WHERE o.customer_id = c.customer_id becomes a fast index lookup instead of a full scan (Indexes chapter).

  2. The optimizer rewrites. MySQL 8 converts EXISTS, NOT EXISTS, IN and NOT IN subqueries into semi-joins and anti-joins where it can. You can see this with EXPLAIN:

EXPLAIN SELECT c.name FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id);
+----+-------------+-------+------+-------------+---------+------+--------------------------------------+
| id | select_type | table | type | key         | key_len | rows | Extra                                |
+----+-------------+-------+------+-------------+---------+------+--------------------------------------+
|  1 | SIMPLE      | c     | ALL  | NULL        | NULL    |    5 | NULL                                 |
|  1 | SIMPLE      | o     | ref  | customer_id | 5       |    1 | Using where; Not exists; Using index |
+----+-------------+-------+------+-------------+---------+------+--------------------------------------+
(some EXPLAIN columns omitted for width)

Both rows have select_type = SIMPLE: the subquery was flattened into an anti-join, using the index on orders.customer_id (InnoDB creates it for the foreign key). Not exists in Extra means MySQL stops looking at orders for a customer as soon as one match is found. By contrast, the "above department average" query shows DEPENDENT SUBQUERY, which means it really is re-executed per outer row (using an index lookup on dept_id).

One caution: EXPLAIN output depends on the table statistics, so you may see a slightly different plan. The plan above is what a freshly loaded company_db gives. With other statistics MySQL 8.4 may instead materialize the distinct customer_id values of orders into a temporary table (shown as <subquery2> with select_type = MATERIALIZED) and anti-join against that. Both are anti-join plans and neither re-runs the subquery per customer.

3. Rewrite with a derived table or window function. Compute all group values once, then join:

SELECT e.name, e.dept_id, e.salary, ROUND(d.avg_sal, 2) AS dept_avg
FROM employees e
JOIN (SELECT dept_id, AVG(salary) AS avg_sal
      FROM employees
      GROUP BY dept_id) AS d ON d.dept_id = e.dept_id
WHERE e.salary > d.avg_sal;
+--------------+---------+-----------+-----------+
| name         | dept_id | salary    | dept_avg  |
+--------------+---------+-----------+-----------+
| Aarav Sharma |      10 | 150000.00 | 113333.33 |
| Sneha Kapoor |      20 | 110000.00 |  76666.67 |
| Karan Patel  |      30 |  70000.00 |  59000.00 |
+--------------+---------+-----------+-----------+
3 rows in set (0.00 sec)

Same 3 employees, but the averages are computed once per department instead of once per employee. The Window Functions chapter shows an even shorter version using AVG(salary) OVER (PARTITION BY dept_id).

A note on LATERAL

MySQL 8.0.14+ also supports LATERAL derived tables: a derived table in FROM that may refer to earlier tables, in other words a correlated derived table. It is handy for "top 1 per group" with several columns:

SELECT c.name, t.order_id, t.amount
FROM customers c
JOIN LATERAL (SELECT o.order_id, o.amount
              FROM orders o
              WHERE o.customer_id = c.customer_id
              ORDER BY o.amount DESC
              LIMIT 1) AS t ON TRUE;
+--------------+----------+---------+
| name         | order_id | amount  |
+--------------+----------+---------+
| Rahul Verma  |     1007 | 5600.00 |
| Kavya Menon  |     1002 | 4800.00 |
| Aditya Joshi |     1004 | 7300.00 |
| Neha Agarwal |     1005 |  950.00 |
+--------------+----------+---------+
4 rows in set (0.00 sec)

Each customer's biggest order. Without LATERAL, a derived table cannot see c.


Common Mistakes

Mistake

What happens

Fix

Forgetting the correlation condition in EXISTS

EXISTS is TRUE (or FALSE) for every row: all or nothing

Link inner and outer: WHERE o.customer_id = c.customer_id

Using NOT IN where the subquery column can be NULL

Empty result

Use NOT EXISTS

Unqualified column that exists only in the outer table

Silent accidental correlation, wrong (or dangerous) result

Always prefix columns with aliases

Same table, no aliases

Ambiguous or wrong references

Use e1/e2 style aliases

Expecting SUM of no rows to be 0

NULL

COALESCE(SUM(...), 0)

Correlated UPDATE reading the same table

ERROR 1093

JOIN to a derived table of precomputed values

Correlated subquery on an unindexed column over a big table

Very slow

Index the correlation column, or rewrite with a join / window function


Interview Questions

What is a correlated subquery?

A subquery that references a column of the outer query, so its result depends on the current outer row and it is logically evaluated once per outer row. It cannot run on its own.

Difference between correlated and non-correlated subqueries?

A non-correlated subquery is independent, runs (logically) once and gives the same value for all outer rows. A correlated one refers to the outer row, runs per row and can give a different value each time.

What does EXISTS return? Does the SELECT list inside it matter?

TRUE if the subquery returns at least one row, FALSE otherwise, never UNKNOWN. The select list is ignored; SELECT 1, SELECT * and even SELECT NULL behave the same.

Why is NOT EXISTS preferred over NOT IN?

If the subquery returns any NULL, NOT IN evaluates to UNKNOWN for non-matching values and returns no rows. NOT EXISTS only checks whether matching rows exist, so NULLs cannot break it. It also lets the optimizer stop at the first match.

IN vs EXISTS: which is faster?

In old MySQL versions EXISTS was often faster for large subqueries. In MySQL 8 both are usually converted to the same semi-join plan, so performance is typically similar. Choose for clarity and correctness (especially for the NOT forms), then check with EXPLAIN.

Find employees who earn more than their department's average.

SELECT e1.name FROM employees e1 WHERE e1.salary > (SELECT AVG(e2.salary) FROM employees e2 WHERE e2.dept_id = e1.dept_id); Returns Aarav, Sneha and Karan for our data.

Find the Nth highest salary without LIMIT or window functions.

SELECT DISTINCT salary FROM employees e1 WHERE N-1 = (SELECT COUNT(DISTINCT salary) FROM employees e2 WHERE e2.salary > e1.salary);

How do you express "for all" in SQL?

With double negation, typically two nested NOT EXISTS: "there is no item for which there is no matching row". This implements relational division.

What does DEPENDENT SUBQUERY mean in EXPLAIN?

The subquery is correlated and MySQL will re-evaluate it for each row of the outer query, rather than flattening it into a join.


Summary

  • A correlated subquery references the outer row and is logically evaluated once per outer row.

  • Typical uses: comparing with a per-group value (department average, latest date), per-row summaries in the SELECT list, Nth highest salary, and correlated UPDATE/DELETE.

  • EXISTS is TRUE when the subquery returns any row; the select list inside is irrelevant. It is a semi-join and returns each outer row at most once.

  • NOT EXISTS is an anti-join and, unlike NOT IN, is not broken by NULLs.

  • Double NOT EXISTS expresses "for all" (relational division).

  • Always alias and qualify columns in subqueries to avoid accidental correlation.

  • For performance, index correlation columns; MySQL 8 flattens many subqueries into joins; derived tables, LATERAL and window functions are alternatives.

Next, we look at a tool for writing if-else logic directly inside a query: the CASE Expression.