Try answering this with what you know so far: "Which employees earn more than the company average?" You need two steps. First find the average salary, then compare every employee with it. You could run two separate queries and copy the number from the first into the second, but that breaks the moment salaries change.
A subquery lets you do both steps in a single statement: the inner query computes the average, and the outer query uses it. Subqueries are one of the most heavily tested SQL topics in placement interviews ("second highest salary", "departments with no employees", "employees above average") and in university exams. In this chapter we cover every kind of subquery that runs independently of the outer query. Subqueries that depend on the outer row (correlated subqueries) and EXISTS get their own chapter next.
All examples use the shared company_db sample database, and every output shown is real MySQL 8.4 output.
What is a Subquery?
A subquery (also called an inner query or nested query) is a
SELECTstatement written inside another SQL statement, enclosed in parentheses. The statement that contains it is called the outer query.
Let us solve the opening question. First, the average on its own:
SELECT AVG(salary) FROM employees;+--------------+
| AVG(salary) |
+--------------+
| 82500.000000 |
+--------------+
1 row in set (0.00 sec)Now put that query, in brackets, exactly where the number would go:
SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);+--------------+-----------+
| name | salary |
+--------------+-----------+
| Aarav Sharma | 150000.00 |
| Priya Iyer | 95000.00 |
| Rohan Mehta | 95000.00 |
| Sneha Kapoor | 110000.00 |
+--------------+-----------+
4 rows in set (0.00 sec)Think of it as "evaluate the brackets first", just like in arithmetic:
Step 1 (inner): SELECT AVG(salary) FROM employees -> 82500
Step 2 (outer): SELECT name, salary FROM employees
WHERE salary > 82500 -> 4 rowsWhy not just write WHERE salary > AVG(salary)? Because aggregate functions are not allowed in WHERE. WHERE looks at one row at a time, while AVG needs all rows (see the Aggregate Functions chapter). The subquery computes the aggregate separately, as a complete query of its own.
Because this inner query does not refer to anything in the outer query, it is called a non-correlated (or simple) subquery. Logically it runs once, and its result is reused for every outer row.
Types of Subqueries by Result Shape
The most useful way to classify subqueries is by what they return, because that decides which operators you can use with them:
Type | Returns | Used with | Example |
|---|---|---|---|
Scalar subquery | 1 row, 1 column (a single value) |
|
|
Column subquery | Many rows, 1 column (a list) |
|
|
Row subquery | 1 row, many columns | Row comparison |
|
Table subquery | Many rows, many columns |
|
|
We can also classify them by where they appear: in WHERE, HAVING, the SELECT list, FROM, or inside INSERT/UPDATE/DELETE. We will see all of these.
Scalar Subqueries
A scalar subquery produces exactly one value, so it can be used anywhere a single value is allowed.
Highest-paid employee
SELECT name, salary
FROM employees
WHERE salary = (SELECT MAX(salary) FROM employees);+--------------+-----------+
| name | salary |
+--------------+-----------+
| Aarav Sharma | 150000.00 |
+--------------+-----------+
1 row in set (0.00 sec)Note: ORDER BY salary DESC LIMIT 1 would also find a top earner, but if two people tied for the maximum it would show only one of them. The subquery version returns everyone who has the maximum salary.
Second highest salary (the famous one)
The second highest salary is the highest salary that is less than the highest salary:
SELECT MAX(salary) AS second_highest
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);+----------------+
| second_highest |
+----------------+
| 110000.00 |
+----------------+
1 row in set (0.00 sec)Inner: MAX(salary) = 150000
Outer: salaries < 150000 = {95000, 95000, 110000, 62000, 58000, 70000,
48000, 82000, 55000}
MAX of those = 110000This handles ties correctly (if two people earned 150000, both are excluded) and returns NULL if there is no second value. For the general "Nth highest salary" there are better tools: a correlated subquery (next chapter) or DENSE_RANK() (Ranking Functions chapter).
Scalar subquery in the SELECT list
Show each Engineering employee next to the company average and the difference:
SELECT name, salary,
(SELECT AVG(salary) FROM employees) AS company_avg,
salary - (SELECT AVG(salary) FROM employees) AS diff
FROM employees
WHERE dept_id = 10;+--------------+-----------+--------------+--------------+
| name | salary | company_avg | diff |
+--------------+-----------+--------------+--------------+
| Aarav Sharma | 150000.00 | 82500.000000 | 67500.000000 |
| Priya Iyer | 95000.00 | 82500.000000 | 12500.000000 |
| Rohan Mehta | 95000.00 | 82500.000000 | 12500.000000 |
+--------------+-----------+--------------+--------------+
3 rows in set (0.00 sec)Notice that the average is computed over all employees even though the outer query keeps only department 10. The subquery is an independent query; the outer WHERE does not affect it.
When a "scalar" subquery is not scalar
If you use = (or <, > ...), the subquery must return at most one row. Departments in Mumbai or Delhi are two rows:
SELECT name FROM employees
WHERE dept_id = (SELECT dept_id FROM departments
WHERE location IN ('Mumbai', 'Delhi'));ERROR 1242 (21000): Subquery returns more than 1 rowThe error is raised at run time, depending on the data. A query that works today can fail tomorrow when a second matching row is inserted. If several rows are possible, use IN (next section).
Returning too many columns is also an error:
SELECT name FROM employees
WHERE dept_id = (SELECT dept_id, dept_name FROM departments WHERE dept_id = 10);ERROR 1241 (21000): Operand should contain 1 column(s)And if the subquery returns no rows, its value is NULL, not an error:
SELECT (SELECT dept_id FROM departments WHERE location = 'Chennai') AS result;+--------+
| result |
+--------+
| NULL |
+--------+
1 row in set (0.00 sec)So WHERE dept_id = (SELECT dept_id FROM departments WHERE location = 'Chennai') becomes dept_id = NULL, which is never TRUE, and the query returns Empty set.
Column Subqueries with IN and NOT IN
IN checks whether a value is in a list. In the SQL Operators chapter the list was typed by hand, IN (20, 30). With a subquery, the list is computed:
SELECT name, dept_id
FROM employees
WHERE dept_id IN (SELECT dept_id FROM departments
WHERE location IN ('Mumbai', 'Delhi'));+--------------+---------+
| name | dept_id |
+--------------+---------+
| Sneha Kapoor | 20 |
| Vikram Singh | 20 |
| Ananya Das | 20 |
| Karan Patel | 30 |
| Meera Nair | 30 |
+--------------+---------+
5 rows in set (0.00 sec)Inner: departments in Mumbai or Delhi -> (20, 30)
Outer: WHERE dept_id IN (20, 30) -> 5 employeesAnother example: customers who ordered in March 2024.
SELECT name
FROM customers
WHERE customer_id IN (SELECT customer_id FROM orders
WHERE order_date BETWEEN '2024-03-01' AND '2024-03-31');+--------------+
| name |
+--------------+
| Rahul Verma |
| Kavya Menon |
| Neha Agarwal |
+--------------+
3 rows in set (0.00 sec)IN subquery vs JOIN: the duplicates difference
"Customers who have placed an order" can be written as a join or with IN. They are not the same:
SELECT c.name
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id;+--------------+
| name |
+--------------+
| Rahul Verma |
| Rahul Verma |
| Rahul Verma |
| Kavya Menon |
| Kavya Menon |
| Aditya Joshi |
| Neha Agarwal |
+--------------+
7 rows in set (0.00 sec)SELECT name
FROM customers
WHERE customer_id IN (SELECT customer_id FROM orders);+--------------+
| name |
+--------------+
| Rahul Verma |
| Kavya Menon |
| Aditya Joshi |
| Neha Agarwal |
+--------------+
4 rows in set (0.00 sec)The join produces one row per matching order (Rahul has 3 orders, so he appears 3 times). The IN version asks a yes/no question per customer ("does this customer appear in the list?"), so each customer appears at most once. This kind of "keep rows of A that have a match in B" query is called a semi-join. Use the join when you need columns from orders; use IN (or EXISTS) when you only need to test for existence.
NOT IN and the NULL trap
Departments that have no employees seem easy:
SELECT dept_id, dept_name
FROM departments
WHERE dept_id NOT IN (SELECT dept_id FROM employees);Empty set (0.00 sec)Empty! But Research (50) clearly has no employees. The reason is Ishita Gupta, whose dept_id is NULL. The subquery returns (10, 10, 10, 20, 20, 20, 30, 30, 40, NULL), and for Research the test becomes:
50 NOT IN (10, 10, ..., 40, NULL)
= 50 <> 10 AND 50 <> 20 AND ... AND 50 <> 40 AND 50 <> NULL
= TRUE AND TRUE AND ... AND TRUE AND UNKNOWN
= UNKNOWN -> row not returnedWe met this trap in the NULL Values chapter; it is far more common with subqueries, because you often do not know whether the inner column contains NULLs. The fix is to exclude them explicitly:
SELECT dept_id, dept_name
FROM departments
WHERE dept_id NOT IN (SELECT dept_id FROM employees
WHERE dept_id IS NOT NULL);+---------+-----------+
| dept_id | dept_name |
+---------+-----------+
| 50 | Research |
+---------+-----------+
1 row in set (0.00 sec)Rule: whenever you write
NOT IN (subquery), either make sure the inner column isNOT NULL, or addWHERE col IS NOT NULLinside. Better still, useNOT EXISTS, which does not have this problem (next chapter).
IN (without NOT) is safe: a NULL in the list can never make a match disappear, it only fails to match.
ANY and ALL
ANY and ALL combine a comparison operator with a column subquery:
x > ANY (subquery): TRUE if x is greater than at least one value in the list. (SOMEis a synonym forANY.)x > ALL (subquery): TRUE if x is greater than every value in the list.
Sales (department 20) salaries are 110000, 62000 and 58000. Who earns more than every Sales employee?
SELECT name, salary
FROM employees
WHERE salary > ALL (SELECT salary FROM employees WHERE dept_id = 20);+--------------+-----------+
| name | salary |
+--------------+-----------+
| Aarav Sharma | 150000.00 |
+--------------+-----------+
1 row in set (0.00 sec)Who earns more than at least one Sales employee?
SELECT name, salary
FROM employees
WHERE salary > ANY (SELECT salary FROM employees WHERE dept_id = 20);+--------------+-----------+
| name | salary |
+--------------+-----------+
| Aarav Sharma | 150000.00 |
| Priya Iyer | 95000.00 |
| Rohan Mehta | 95000.00 |
| Sneha Kapoor | 110000.00 |
| Vikram Singh | 62000.00 |
| Karan Patel | 70000.00 |
| Arjun Reddy | 82000.00 |
+--------------+-----------+
7 rows in set (0.00 sec)A handy way to remember them:
Expression | Same as | Here |
|---|---|---|
|
| > 110000 |
|
| > 58000 |
|
| < 58000 |
|
| < 110000 |
|
| |
|
|
The empty-list surprise
The "same as MAX" shortcut breaks when the subquery returns no rows. Department 50 has no employees:
SELECT name, salary
FROM employees
WHERE salary > ALL (SELECT salary FROM employees WHERE dept_id = 50);+--------------+-----------+
| name | salary |
+--------------+-----------+
| Aarav Sharma | 150000.00 |
| Priya Iyer | 95000.00 |
| Rohan Mehta | 95000.00 |
| Sneha Kapoor | 110000.00 |
| Vikram Singh | 62000.00 |
| Ananya Das | 58000.00 |
| Karan Patel | 70000.00 |
| Meera Nair | 48000.00 |
| Arjun Reddy | 82000.00 |
| Ishita Gupta | 55000.00 |
+--------------+-----------+
10 rows in set (0.00 sec)All 10 rows! "x is greater than every element of an empty list" is vacuously true: there is no element that disproves it. In contrast, salary > (SELECT MAX(salary) FROM employees WHERE dept_id = 50) compares with NULL and returns Empty set. And > ANY on an empty list is always FALSE (there is no element to be greater than). Remember these edge cases; they are favourite MCQ material.
Row Subqueries
A subquery can return one row with several columns, compared against a row constructor (col1, col2). Who has the same department and salary as Priya (emp 2)?
SELECT name, dept_id, salary
FROM employees
WHERE (dept_id, salary) = (SELECT dept_id, salary
FROM employees WHERE emp_id = 2);+-------------+---------+----------+
| name | dept_id | salary |
+-------------+---------+----------+
| Priya Iyer | 10 | 95000.00 |
| Rohan Mehta | 10 | 95000.00 |
+-------------+---------+----------+
2 rows in set (0.00 sec)The multi-column form also works with IN and a subquery returning many rows. This gives a neat answer to "highest-paid employee in each department":
SELECT name, dept_id, salary
FROM employees
WHERE (dept_id, salary) IN (SELECT dept_id, MAX(salary)
FROM employees
GROUP BY dept_id)
ORDER BY dept_id;+--------------+---------+-----------+
| name | dept_id | salary |
+--------------+---------+-----------+
| Aarav Sharma | 10 | 150000.00 |
| Sneha Kapoor | 20 | 110000.00 |
| Karan Patel | 30 | 70000.00 |
| Arjun Reddy | 40 | 82000.00 |
+--------------+---------+-----------+
4 rows in set (0.00 sec)Inner (GROUP BY dept_id): (10,150000) (20,110000) (30,70000) (40,82000) (NULL,55000)
Outer: keep employees whose (dept_id, salary) pair is in that listIshita's group (NULL, 55000) is in the list, yet she is not in the result. Comparing her (NULL, 55000) with (NULL, 55000) involves NULL = NULL, which is UNKNOWN. NULLs again!
Subqueries in FROM: Derived Tables
A subquery in the FROM clause produces a temporary result table that the outer query reads like a normal table. This is called a derived table (other databases call it an inline view).
Departments whose average salary is above 80,000:
SELECT dept_id, avg_sal
FROM (SELECT dept_id, AVG(salary) AS avg_sal
FROM employees
WHERE dept_id IS NOT NULL
GROUP BY dept_id) AS dept_avg
WHERE avg_sal > 80000;+---------+---------------+
| dept_id | avg_sal |
+---------+---------------+
| 10 | 113333.333333 |
| 40 | 82000.000000 |
+---------+---------------+
2 rows in set (0.00 sec)The inner query builds a small table dept_avg(dept_id, avg_sal); the outer query filters it. Because avg_sal is now an ordinary column of dept_avg, the outer WHERE can use it. (You could also write this with HAVING; derived tables become essential when you need to aggregate twice, as below.)
Derived tables let you do "aggregate of an aggregate", which is impossible in one level, e.g. MAX(AVG(salary)) is not allowed in MySQL. The best department average:
SELECT MAX(avg_sal) AS best_dept_avg
FROM (SELECT dept_id, AVG(salary) AS avg_sal
FROM employees
GROUP BY dept_id) AS t;+---------------+
| best_dept_avg |
+---------------+
| 113333.333333 |
+---------------+
1 row in set (0.00 sec)Every derived table needs a name
Forget the alias and MySQL complains:
SELECT MAX(avg_sal)
FROM (SELECT dept_id, AVG(salary) AS avg_sal
FROM employees GROUP BY dept_id);ERROR 1248 (42000): Every derived table must have its own aliasAlso give every computed column inside a derived table an alias (like avg_sal), so the outer query can refer to it by a sensible name. When derived tables get long or nested, a Common Table Expression (WITH) is usually more readable; that is covered in its own chapter.
Subqueries in HAVING
HAVING filters groups, and it can compare against a subquery too. Departments whose average salary is above the company average:
SELECT dept_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY dept_id
HAVING AVG(salary) > (SELECT AVG(salary) FROM employees);+---------+---------------+
| dept_id | avg_salary |
+---------+---------------+
| 10 | 113333.333333 |
+---------+---------------+
1 row in set (0.00 sec)Only Engineering (113,333) is above the company average of 82,500. Marketing (82,000) just misses, and Research (50) has no employees at all, so it forms no group.
Subqueries in INSERT, UPDATE and DELETE
Subqueries are not limited to SELECT. The following demos modify data, so they were run on a copy of the sample database. Never practise data changes on data you care about.
UPDATE with a subquery
Give everyone in HR a 5,000 raise, without hard-coding HR's id:
UPDATE employees
SET salary = salary + 5000
WHERE dept_id = (SELECT dept_id FROM departments WHERE dept_name = 'HR');Query OK, 2 rows affected (0.01 sec)
Rows matched: 2 Changed: 2 Warnings: 0Karan's salary became 75,000 and Meera's 53,000.
INSERT ... SELECT with a subquery
CREATE TABLE vip_customers (customer_id INT PRIMARY KEY, name VARCHAR(50));
INSERT INTO vip_customers (customer_id, name)
SELECT customer_id, name
FROM customers
WHERE customer_id IN (SELECT customer_id FROM orders WHERE amount > 5000);
SELECT * FROM vip_customers;+-------------+--------------+
| customer_id | name |
+-------------+--------------+
| 101 | Rahul Verma |
| 103 | Aditya Joshi |
+-------------+--------------+
2 rows in set (0.00 sec)DELETE with a subquery
DELETE FROM orders
WHERE customer_id IN (SELECT customer_id FROM customers WHERE city = 'Mumbai');Query OK, 1 row affected (0.01 sec)Neha Agarwal is the only Mumbai customer, so her single order (1005) was deleted.
MySQL error 1093: reading the table you are changing
Suppose we want to set Ishita's salary to the company average:
UPDATE employees
SET salary = (SELECT AVG(salary) FROM employees)
WHERE emp_id = 10;ERROR 1093 (HY000): You can't specify target table 'employees' for update in FROM clauseMySQL does not allow an UPDATE or DELETE to read, in a subquery, the same table it is modifying. (The reason: the rows would be changing while the subquery reads them, so the result could depend on the order in which rows are updated.) The usual workaround is to wrap the read in a derived table, which MySQL materialises into a temporary result first:
UPDATE employees
SET salary = (SELECT avg_sal
FROM (SELECT AVG(salary) AS avg_sal FROM employees) AS t)
WHERE emp_id = 10;Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0Ishita's salary is now 83,500 (the average after the HR raise: 835,000 / 10). Other databases such as PostgreSQL allow the first form directly; error 1093 is a MySQL-specific restriction that interviewers who use MySQL love to ask about.
Another MySQL Limitation: LIMIT inside IN
"Employees with one of the top 3 salaries" looks natural like this:
SELECT name FROM employees
WHERE salary IN (SELECT salary FROM employees ORDER BY salary DESC LIMIT 3);ERROR 1235 (42000): This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'MySQL does not allow LIMIT in a subquery used with IN, ALL, ANY or SOME. Again, a derived table gets around it:
SELECT name, salary
FROM employees
WHERE salary IN (SELECT salary
FROM (SELECT DISTINCT salary FROM employees
ORDER BY salary DESC LIMIT 3) AS top3);+--------------+-----------+
| name | salary |
+--------------+-----------+
| Aarav Sharma | 150000.00 |
| Priya Iyer | 95000.00 |
| Rohan Mehta | 95000.00 |
| Sneha Kapoor | 110000.00 |
+--------------+-----------+
4 rows in set (0.00 sec)The top 3 distinct salaries are 150000, 110000 and 95000, and two people earn 95000, so we get 4 employees. (Without DISTINCT, the top 3 values would be 150000, 110000, 95000 as well here, but in general duplicates would use up the 3 slots.)
Subquery or Join?
Many subqueries can be rewritten as joins and vice versa. Some guidance:
Situation | Prefer |
|---|---|
You need columns from both tables in the output | JOIN |
You only need to test whether a match exists |
|
Compare with an aggregate (average, max) | Scalar subquery |
Aggregate of an aggregate, or filter on a computed column | Derived table (or CTE) |
"Rows with no match" |
|
On performance: the old advice "subqueries are always slow, use joins" comes from very old MySQL versions. MySQL 8's optimizer transforms IN and EXISTS subqueries into semi-joins internally and materialises derived tables when useful, so a well-written subquery and the equivalent join usually perform about the same. Write the version that expresses the question most clearly, then check with EXPLAIN (Indexes chapter) if it is slow.
Common Mistakes
Mistake | What happens | Fix |
|---|---|---|
| ERROR 1242 (possibly only after data grows) | Use |
Subquery returns 2 columns where 1 is expected | ERROR 1241 Operand should contain 1 column(s) | Select only the needed column |
| Empty result | Add |
Derived table without alias | ERROR 1248 |
|
| ERROR 1111 Invalid use of group function | Use a scalar subquery |
Updating a table while reading it in a subquery | ERROR 1093 | Wrap the read in a derived table |
| ERROR 1235 | Wrap in a derived table |
Assuming | Every row returned | Remember: ALL over empty set is TRUE |
Interview Questions
What is a subquery? What are its types?
A SELECT nested inside another statement, in parentheses. By result shape: scalar (single value), column (one column, many rows), row (one row, many columns) and table (many rows and columns). By dependency: non-correlated (independent, runs once) and correlated (refers to the outer row).
Find the second highest salary.
SELECT MAX(salary) FROM employees WHERE salary < (SELECT MAX(salary) FROM employees); It returns 110000 for our data, handles ties at the top, and returns NULL if there is no second salary.
Why can't you write WHERE salary > AVG(salary)?
Aggregates are not allowed in WHERE, because WHERE is evaluated row by row before grouping. Compute the aggregate in a subquery: WHERE salary > (SELECT AVG(salary) FROM employees).
What happens when a scalar subquery returns no rows? More than one row?
No rows: its value is NULL. More than one row: error 1242 "Subquery returns more than 1 row".
Why might NOT IN return no rows?
If the subquery result contains a NULL, x NOT IN (...) evaluates to UNKNOWN for every x that is not in the list, so nothing is returned. Exclude NULLs inside the subquery or use NOT EXISTS.
What is the difference between ANY and ALL?
> ANY is true if the value beats at least one element (like > MIN); > ALL is true if it beats every element (like > MAX). On an empty subquery, ANY is false and ALL is true.
What is a derived table? Why does it need an alias?
A subquery in the FROM clause whose result is used as a temporary table. It needs a name so that the outer query can refer to it and its columns; MySQL raises error 1248 otherwise.
What is MySQL error 1093 and how do you work around it?
MySQL forbids an UPDATE/DELETE from selecting from the same table in a subquery. Wrap that subquery in a derived table so it is materialised first, or compute the value in a separate step.
IN subquery vs JOIN: what is the difference in output?
A join returns one row per matching pair, so a customer with 3 orders appears 3 times. IN is a semi-join: each outer row appears at most once.
Summary
A subquery is a query in parentheses inside another statement; a non-correlated subquery runs independently and logically once.
Scalar subqueries return one value and work with
=,<,>and in the SELECT list; zero rows give NULL, multiple rows give error 1242.Column subqueries feed
IN,NOT IN,ANYandALL.NOT INbreaks when the list has a NULL.> ALLbehaves like > MAX and> ANYlike > MIN, except on an empty list.Row subqueries compare several columns at once:
(a, b) IN (SELECT ...).Derived tables (subqueries in FROM) must have an alias and allow aggregate-of-aggregate.
Subqueries also work in HAVING, INSERT, UPDATE and DELETE; watch for MySQL errors 1093 and 1235.
So far the inner query never looked at the outer row. In the next chapter, Correlated Subqueries & EXISTS, the inner query will change for every outer row, which lets us answer questions like "employees who earn more than their own department's average".