MySQL Subquery: Write a Query Inside Another Query

A subquery (or inner query) is a SELECT written inside another statement. The result of the inner query is used by the outer one.

query.sql

SQL

CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    category VARCHAR(50) NOT NULL,
    price DECIMAL(8,2) NOT NULL,
    stock INT NOT NULL DEFAULT 0,
    added_on DATE NOT NULL
);

INSERT INTO products (name, category, price, stock, added_on) VALUES
    ('Notebook',      'stationery',  80.00, 120, '2026-01-10'),
    ('Gel Pen',       'stationery',  15.50, 300, '2026-01-12'),
    ('Backpack',      'bags',       999.00,  25, '2026-02-01'),
    ('Marker Set',    'stationery', 145.00,   0, '2026-02-15'),
    ('Desk Lamp',     'home',       650.00,  40, '2026-03-03'),
    ('Water Bottle',  'home',       220.00,  75, '2026-03-20'),
    ('Laptop Sleeve', 'bags',       450.00,  18, '2026-04-02'),
    ('Sticky Notes',  'stationery',  35.00, 210, '2026-04-18');

“Which products cost more than the average price?” You need two steps: find the average, then compare. A subquery does both in one statement:

query.sql

SQL

SELECT name, price
FROM products
WHERE price > (SELECT AVG(price) FROM products)
ORDER BY price DESC;

Output

OUTPUT

+---------------+--------+
| name          | price  |
+---------------+--------+
| Backpack      | 999.00 |
| Desk Lamp     | 650.00 |
| Laptop Sleeve | 450.00 |
+---------------+--------+
3 rows in set

Read it from the inside out. The inner query SELECT AVG(price) FROM products runs first and returns one number (324.3125). The outer query then becomes WHERE price > 324.3125.

The four shapes of a subquery

Shape	Returns	Used with
Scalar	One value (one row, one column)	`=`, `>`, `<`, in `SELECT` or `WHERE`
Column	One column, several rows	`IN`, `ANY`, `ALL`
Row	One row, several columns	Row comparison: `(a, b) = (SELECT ...)`
Table	Rows and columns	In the `FROM` clause (a derived table), or with `EXISTS`

Where a subquery can appear

query.sql

SQL

1
2

-- in SELECT: a scalar subquery gives one value per row
SELECT name, price, price - (SELECT MIN(price) FROM products) AS above_cheapest FROM products ORDER BY id LIMIT 3;

Output

OUTPUT

+----------+--------+----------------+
| name     | price  | above_cheapest |
+----------+--------+----------------+
| Notebook |  80.00 |          64.50 |
| Gel Pen  |  15.50 |           0.00 |
| Backpack | 999.00 |         983.50 |
+----------+--------+----------------+
3 rows in set

query.sql

SQL

1
2

-- in WHERE with IN: a column subquery
SELECT name FROM products WHERE category IN (SELECT category FROM products WHERE price > 500) ORDER BY name;

Output

OUTPUT

+---------------+
| name          |
+---------------+
| Backpack      |
| Desk Lamp     |
| Laptop Sleeve |
| Water Bottle  |
+---------------+
4 rows in set

query.sql

SQL

1
2

-- in FROM: a derived table (needs an alias)
SELECT category, avg_price FROM (SELECT category, ROUND(AVG(price), 2) AS avg_price FROM products GROUP BY category) AS category_averages WHERE avg_price > 100 ORDER BY category;

Output

OUTPUT

+----------+-----------+
| category | avg_price |
+----------+-----------+
| bags     |    724.50 |
| home     |    435.00 |
+----------+-----------+
2 rows in set

query.sql

SQL

1
2

-- with a row constructor: the product(s) with the highest price in their category
SELECT name, category, price FROM products WHERE (category, price) IN (SELECT category, MAX(price) FROM products GROUP BY category) ORDER BY category;

Output

OUTPUT

+------------+------------+--------+
| name       | category   | price  |
+------------+------------+--------+
| Backpack   | bags       | 999.00 |
| Desk Lamp  | home       | 650.00 |
| Marker Set | stationery | 145.00 |
+------------+------------+--------+
3 rows in set

Subqueries need brackets

A subquery is always wrapped in round brackets. A subquery in FROM also needs an alias.

Scalar subqueries must return one value

If the inner query returns more than one row where one value is expected, MySQL raises error 1242:

query.sql

SQL

SELECT name FROM products WHERE price = (SELECT price FROM products WHERE category = 'bags');

Output

OUTPUT

ERROR 1242 (21000): Subquery returns more than 1 row

Two products are in the bags category, so the subquery returns two prices. Use IN instead of =, or an aggregate (MAX, MIN), or a stricter condition.

Nesting

Subqueries can contain subqueries. It is legal, and gets hard to read after two levels. Split a long query into CTEs (WITH clauses) for clarity (CTEs).

Subquery or join?

Many subqueries can also be written as joins, and the reverse. In MySQL 8, the optimiser often turns them into the same plan. Choose the form that is easiest to read and then check with EXPLAIN (Subquery vs Join).

Rules

A subquery in a comparison cannot use ORDER BY meaningfully (it is ignored unless combined with LIMIT).
LIMIT is not allowed in a subquery used with IN, ANY, ALL or SOME (Subquery Pitfalls).
In UPDATE and DELETE, a subquery cannot read the table that is being changed, unless wrapped in a derived table.

lightbulb

Test the inside first

Run the inner query alone. If it returns the values you expect, the outer query is usually easy to get right.