MySQL: A Detailed Explanation of MySQL Subqueries and EXISTS
Last updated: 2026-08-26
Subqueries are a feature of SQL that allows for nesting—the results of one query are used as a condition for another query.
This lesson provides a systematic explanation of the various forms of subqueries and their optimization.
graph TB
A[Subquery Categories] --> B[Tag-Based Query<br/>Return a single value]
A --> C[Liezi Query<br/>Return a column]
A --> D[Row-based queries<br/>Return a line]
A --> E[Table Child Query<br/>Back to Table]
C --> C1[IN / NOT IN]
C --> C2[ANY / ALL]
E --> E1[FROM Derivation Table]
A --> F[EXISTS<br/>Existence Check]
A --> G[Correlated Subqueries<br/>Citation Format]
1. What You'll Learn
- Indexed query (returns a single value)
- Column-based query (returns a single column)
- Row-based query (returns one row)
- Table subquery (returns a table)
- EXISTS/NOT EXISTS
2. Real-Life Scenarios
(1) Pain Point: Cannot Be Achieved in One Step
To find "employees whose salaries are higher than the average salary," you need to calculate the average first and then make the comparison.
(2) Solutions for Subqueries
SQL
SELECT * FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);
3. Standard Quantum Query
Return a single row and a single column.
▶ Example: Tagged Query
SQL
-- Employees with above-average salaries
SELECT * FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);
-- The highest-paid employee
SELECT * FROM employees
WHERE salary = (SELECT MAX(salary) FROM employees);
-- Customers with the most recent orders
SELECT * FROM customers
WHERE id = (SELECT customer_id FROM orders ORDER BY order_date DESC LIMIT 1);
Output:
TEXT
📖 Display only
Output displayed
4. Queries on the Book of Liezi
Returns a single column with multiple rows. Used in conjunction with IN, NOT IN, ANY, and ALL.
▶ Example: IN Subquery
SQL
-- Customers with orders
SELECT * FROM customers
WHERE id IN (SELECT DISTINCT customer_id FROM orders);
-- Customers with no orders
SELECT * FROM customers
WHERE id NOT IN (SELECT DISTINCT customer_id FROM orders WHERE customer_id IS NOT NULL);
Output:
TEXT
📖 Display only
---+-------+----------------
id | name | email
---+-------+----------------
1 | Alice | alice@email.com
2 | Bob | bob@email.com
---+-------+----------------
2 rows in set
---+-------+----------------
id | name | email
---+-------+----------------
1 | Alice | alice@email.com
2 | Bob | bob@email.com
---+-------+----------------
2 rows in set
▶ Example: ANY/ALL Subqueries
SQL
-- Salary higher than Sales Any employee in the department（Higher than the minimum）
SELECT * FROM employees
WHERE salary > ANY (SELECT salary FROM employees WHERE department = 'Sales');
-- Salary higher than Sales All employees in the department（Higher than the highest）
SELECT * FROM employees
WHERE salary > ALL (SELECT salary FROM employees WHERE department = 'Sales');
Output:
TEXT
📖 Display only
Output displayed
5. EXISTS Subquery
Check whether the subquery returns any results.
▶ Example: EXISTS
SQL
-- Customers with orders
SELECT * FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);
-- Customers with no orders
SELECT * FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);
Output:
TEXT
📖 Display only
Output displayed
(1) EXISTS vs IN Comparison
| Dimension | EXISTS | IN |
|---|---|---|
| Execution Method | Rows-by-row check of the subquery against the table | Execute the subquery first, then perform the match |
| Use Cases | Small outer table, large subquery | Large outer table, small subquery |
| Handling NULL Values | Security | The "NOT IN" NULL Trap |
| Performance | Generally better | Better when the subquery result set is small |
6. Subtable Queries (Derived Tables)
Subqueries as temporary tables.
▶ Example: FROM Subquery
Output:
TEXT
📖 Display only
+-------+
| * |
+-------+
| 99.99 |
+-------+
1 row in set
+------+--------------+--------------+
| name | total_orders | total_amount |
+------+--------------+--------------+
| 5 | 5 | 5 |
+------+--------------+--------------+
1 row in set
SQL
-- The highest-paid person in each department
SELECT e.* FROM employees e
INNER JOIN (
SELECT department, MAX(salary) AS max_salary
FROM employees
GROUP BY department
) dept_max ON e.department = dept_max.department AND e.salary = dept_max.max_salary;
-- Customer Order Statistics
SELECT c.name, order_stats.total_orders, order_stats.total_amount
FROM customers c
INNER JOIN (
SELECT customer_id, COUNT(*) AS total_orders, SUM(amount) AS total_amount
FROM orders
GROUP BY customer_id
) order_stats ON c.id = order_stats.customer_id;
Output:
TEXT
📖 Display only
Output displayed
7. Correlated Subqueries
The subquery references fields from the outer table.
▶ Example: Correlated Subqueries
Output:
TEXT
📖 Display only
+-------+
| * |
+-------+
| 99.99 |
+-------+
1 row in set
+-------+
| * |
+-------+
| 30.00 |
+-------+
1 row in set
SQL
-- The highest-paid employee in each department
SELECT * FROM employees e1
WHERE salary = (
SELECT MAX(salary) FROM employees e2
WHERE e2.department = e1.department
);
-- Employees whose salaries are higher than the department average
SELECT * FROM employees e1
WHERE salary > (
SELECT AVG(salary) FROM employees e2
WHERE e2.department = e1.department
);
Output:
TEXT
📖 Display only
Output displayed
8. Subquery Optimization
| Strategy | Description |
|---|---|
| Use JOINs Instead of Subqueries | The MySQL optimizer can usually handle this, but JOINs are more intuitive |
| Use EXISTS instead of IN | EXISTS is usually faster when dealing with large datasets |
| Avoid correlated subqueries | Rewrite as a JOIN whenever possible |
| Indexing subqueries | Indexing join/filter fields in subqueries |
❓ FAQ
Q Which is faster, a subquery or a JOIN?
A Generally, a JOIN is faster (the optimizer can optimize it better). However, it depends on the data distribution; use EXPLAIN to compare.
Q How many levels of subqueries can be nested?
A MySQL limits the nesting depth (typically 255 levels), but it is generally recommended not to exceed 3 levels.
Q Is there a NULL trap with NOT IN?
A Yes.
NOT IN (1, 2, NULL) always returns NULL. Use NOT EXISTS instead.Q Which is faster, a subquery or a JOIN?
A It depends on the data volume and indexes; generally, a JOIN is more efficient. We recommend using EXPLAIN to compare the actual execution plans.
Q How many levels of subqueries can be nested?
A Theoretically, there is no limit, but readability becomes extremely poor beyond three levels; it is recommended to use a CTE (WITH clause) instead.
📖 Summary
- Standard Quantity Query Returns a single value; compare using
= / > / < - Liezi Query returns multiple rows; use
IN / NOT IN / ANY / ALL - EXISTS checks whether a subquery returns any results; it is generally more efficient than IN
- Subquery: Used in the
FROMclause as a derived table - Correlated subquery references a field from an external table and is executed once for each row
- Optimization principles: Use JOIN instead of subqueries, use EXISTS instead of NOT IN
📝 Exercises
-
Basic Problem (Difficulty ⭐): Use subqueries to find employees whose salaries are higher than the average salary.
-
Advanced Problem (Difficulty: ⭐⭐): Use
EXISTSto find customers who have never placed an order. -
Challenge Question (Difficulty: ⭐⭐⭐): Use a correlated subquery to find the employee with the highest salary in each department.
