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 rLet 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 |
| 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 = NULLis never TRUE, so the subquery averages zero rows and returns NULL.55000 > NULLis 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) FALSEEach 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 -> keptEXISTS 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(orLEFT JOIN ... WHERE right.key IS NULL) instead ofNOT 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 dropRead 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: 0SELECT 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 clauseThis 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:
Indexes on the correlation column. The inner query
WHERE o.customer_id = c.customer_idbecomes a fast index lookup instead of a full scan (Indexes chapter).The optimizer rewrites. MySQL 8 converts
EXISTS,NOT EXISTS,INandNOT INsubqueries into semi-joins and anti-joins where it can. You can see this withEXPLAIN:
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: |
Using | Empty result | Use |
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 |
Expecting | NULL |
|
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.
EXISTSis 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 EXISTSis an anti-join and, unlikeNOT IN, is not broken by NULLs.Double
NOT EXISTSexpresses "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.