How to Perform Subqueries in MySQL Database

I remember the exact moment subqueries clicked for me. I was trying to find every customer who had placed an order above the average order value, and I kept trying to do it in one flat query with a WHERE clause referencing an aggregate directly — which, of course, MySQL rejected. A colleague looked over my shoulder and said, “just nest a query inside the query.” That one sentence opened up a way of thinking about SQL that I still use every single day, so I want to walk you through subqueries the way I wish someone had walked me through them.

What a Subquery Actually Is

A subquery — sometimes called an inner query or nested query — is simply a SELECT statement embedded inside another SQL statement. It runs first (conceptually), and its result feeds into the outer query. You can use subqueries inside SELECT, FROM, WHERE, HAVING, and even INSERT, UPDATE, and DELETE statements.

I like to think of a subquery as asking a small question to help answer a bigger question. “What’s the average order value?” is the small question. “Which customers exceeded it?” is the big question. Subqueries let me chain those together in one statement instead of running two separate queries and gluing the results together in application code.

Setting Up Sample Tables

Let me build a small schema so every example below is runnable.

CREATE TABLE customers (
    customer_id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    city VARCHAR(50)
);

CREATE TABLE orders (
    order_id INT AUTO_INCREMENT PRIMARY KEY,
    customer_id INT,
    order_total DECIMAL(10,2),
    order_date DATE,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

INSERT INTO customers (name, city) VALUES
('Hamza', 'Lahore'), ('Zainab', 'Karachi'), ('Ali', 'Lahore'), ('Fatima', 'Islamabad');

INSERT INTO orders (customer_id, order_total, order_date) VALUES
(1, 250.00, '2026-01-05'),
(1, 400.00, '2026-02-14'),
(2, 150.00, '2026-01-20'),
(3, 700.00, '2026-03-01'),
(4, 90.00, '2026-03-10');

Scalar Subqueries

A scalar subquery returns a single value — one row, one column. This is the simplest form and the one I use constantly for comparisons.

SELECT name, order_total
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
WHERE order_total > (SELECT AVG(order_total) FROM orders);

Output:

+--------+-------------+
| name   | order_total |
+--------+-------------+
| Hamza  |      400.00 |
| Ali    |      700.00 |
+--------+-------------+

The inner query (SELECT AVG(order_total) FROM orders) computes 318.00, and the outer query keeps only orders above that value. This is exactly the pattern that first taught me how powerful subqueries are — the alternative would have meant two round trips or a temporary variable.

Subqueries in the FROM Clause (Derived Tables)

A subquery can also stand in as a virtual table inside FROM. MySQL calls this a derived table, and I use it constantly when I need to aggregate first and then filter or join on that aggregate.

SELECT customer_id, total_spent
FROM (
    SELECT customer_id, SUM(order_total) AS total_spent
    FROM orders
    GROUP BY customer_id
) AS customer_totals
WHERE total_spent > 300;

Every derived table must have an alias — I named mine customer_totals — or MySQL throws a syntax error.

Subqueries in SELECT (Column Subqueries)

I can also put a scalar subquery directly into the column list, which is handy for adding a computed value alongside each row without a JOIN.

SELECT name,
       (SELECT COUNT(*) FROM orders o WHERE o.customer_id = c.customer_id) AS order_count
FROM customers c;

Output:

+--------+-------------+
| name   | order_count |
+--------+-------------+
| Hamza  |           2 |
| Zainab |           1 |
| Ali    |           1 |
| Fatima |           1 |
+--------+-------------+

This is called a correlated subquery because the inner query references c.customer_id from the outer query — it can’t run independently. I’ll cover correlated subqueries in more depth shortly because they behave very differently from uncorrelated ones performance-wise.

IN, ANY, ALL, and EXISTS

These operators let a subquery return multiple rows and still participate meaningfully in a WHERE clause.

IN

SELECT name FROM customers
WHERE customer_id IN (SELECT customer_id FROM orders WHERE order_total > 300);

ANY / SOME

SELECT name FROM customers c
WHERE 500 > ANY (SELECT order_total FROM orders WHERE customer_id = c.customer_id);

This reads as “500 is greater than at least one of that customer’s order totals.”

ALL

SELECT name FROM customers c
WHERE 500 > ALL (SELECT order_total FROM orders WHERE customer_id = c.customer_id);

This reads as “500 is greater than every one of that customer’s order totals” — a subtly stricter condition than ANY.

EXISTS

SELECT name FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id AND o.order_total > 600);

I personally reach for EXISTS far more often than IN once a table gets large, because EXISTS stops scanning as soon as it finds one matching row, while IN typically has to materialize the full result set of the subquery first — though modern MySQL’s optimizer has gotten smarter about rewriting these into semi-joins under the hood.

Correlated vs. Non-Correlated Subqueries

This is the distinction that matters most for performance:

• A non-correlated subquery runs once, independently of the outer query, and its result is reused for every row of the outer query.
• A correlated subquery runs once per row of the outer query, because it references a column from the outer row.

flowchart TD
    A[Outer Query starts] --> B{Correlated?}
    B -->|No| C[Run inner query once]
    C --> D[Reuse single result for all outer rows]
    B -->|Yes| E[Run inner query once per outer row]
    E --> F[Slower on large tables, but flexible]

Correlated subqueries are extremely readable and expressive, but on large tables they can be much slower than an equivalent JOIN, because MySQL may execute the inner query thousands of times. I always check EXPLAIN before shipping a correlated subquery in a hot path.

Subqueries with INSERT, UPDATE, and DELETE

Subqueries aren’t limited to SELECT statements.

-- INSERT using a subquery
INSERT INTO high_value_customers (customer_id, name)
SELECT customer_id, name FROM customers
WHERE customer_id IN (SELECT customer_id FROM orders WHERE order_total > 500);

-- UPDATE using a subquery
UPDATE customers c
SET c.city = 'Unknown'
WHERE c.customer_id NOT IN (SELECT customer_id FROM orders);

-- DELETE using a subquery
DELETE FROM customers
WHERE customer_id NOT IN (SELECT DISTINCT customer_id FROM orders);

One gotcha I hit early on: in older MySQL versions, you couldn’t reference the same table you’re updating or deleting from inside a direct subquery — you’d get error 1093 (“You can’t specify target table for update in FROM clause”). The common workaround is wrapping the subquery in a derived table:

DELETE FROM customers
WHERE customer_id NOT IN (
    SELECT customer_id FROM (SELECT DISTINCT customer_id FROM orders) AS tmp
);

Wrapping it in an extra derived-table layer forces MySQL to materialize the result first, sidestepping the restriction.

Subqueries vs. JOINs vs. CTEs

I get asked constantly whether a subquery, a JOIN, or a Common Table Expression (CTE) is “better.” My honest answer is: it depends on readability and, sometimes, on the optimizer’s behavior for that specific query.

Since MySQL 8.0, I often rewrite complex nested subqueries as CTEs purely for readability:

WITH customer_totals AS (
    SELECT customer_id, SUM(order_total) AS total_spent
    FROM orders
    GROUP BY customer_id
)
SELECT c.name, ct.total_spent
FROM customers c
JOIN customer_totals ct ON c.customer_id = ct.customer_id
WHERE ct.total_spent > 300;

How MySQL Executes Subqueries Internally

Under the hood, MySQL’s optimizer tries to transform many subqueries into semi-joins or anti-joins whenever possible, which is a major performance improvement introduced in MySQL 5.6+ and refined further in 8.0. A semi-join returns rows from the outer table that have at least one match in the subquery, without duplicating rows — conceptually similar to EXISTS, but executed with join-style algorithms (like a hash join) instead of row-by-row execution.

I always run EXPLAIN FORMAT=JSON on a subquery-heavy statement to see whether MySQL materialized the subquery, converted it to a semi-join, or is executing it as a genuinely dependent subquery (which shows up as DEPENDENT SUBQUERY in EXPLAIN output — a red flag on large tables).

EXPLAIN SELECT name FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id);

Real-World DBA Scenarios

• Finding orphaned records: subqueries with NOT IN or NOT EXISTS are my go-to for finding rows in a child table with no matching parent, before running a cleanup migration.
• Top-N per group: I frequently use a correlated subquery to find, say, the most recent order per customer, though I’ve increasingly replaced these with window functions (ROW_NUMBER() OVER (PARTITION BY ...)) since MySQL 8.0 introduced them, because they’re usually faster and clearer.
• Data validation before a migration: before dropping a legacy table, I run subqueries to confirm no active table still references it.
• Report building: derived tables let me pre-aggregate large fact tables before joining them to dimension tables, which keeps report queries fast.

Security Considerations

Subqueries themselves don’t introduce a unique security risk beyond what any dynamic SQL does, but I’ve learned to be careful in two areas:

1. Dynamic subquery construction in application code (string-concatenating user input into a subquery) is just as vulnerable to SQL injection as any other query — always use parameterized queries or prepared statements.
2. Overly broad correlated subqueries in permission-checking logic (e.g., “does this user have access to this record?”) can be a performance and even correctness risk if the correlation condition is wrong, silently granting access to more rows than intended. I always write a unit test for the negative case — rows that should NOT match.

Troubleshooting Common Subquery Errors

That last one is worth expanding on because it’s bitten me before: if the subquery in a NOT IN clause returns even a single NULL, the entire NOT IN condition can evaluate to UNKNOWN for every row, returning zero results. This is one of the strongest reasons I default to NOT EXISTS over NOT IN whenever the subquery column might contain NULLs.

FAQs

Can a subquery reference the outer query’s table? Yes, that’s a correlated subquery, and it’s one of the most powerful subquery patterns, though it can be slower on large datasets.

Are subqueries slower than JOINs? Not inherently — a non-correlated subquery or one the optimizer converts to a semi-join can perform identically to a JOIN. Correlated subqueries are the ones most likely to underperform on large tables.

Can I use ORDER BY inside a subquery? Yes, but MySQL doesn’t guarantee the outer query preserves that order unless you also add ORDER BY to the outer query — a subquery’s own ordering isn’t reliable once it’s just feeding data into a bigger statement.

What’s the difference between a subquery and a CTE? Functionally similar for a single reference, but a CTE (WITH) is defined once and can be referenced multiple times in the same statement, and it’s generally easier to read for complex, multi-step logic.

Does MySQL support recursive subqueries? Not as classic subqueries, but MySQL 8.0 added recursive Common Table Expressions (WITH RECURSIVE) for hierarchical data like org charts or category trees.

Common Interview Questions

1. What’s the difference between a correlated and a non-correlated subquery?
2. Why might NOT IN return unexpected empty results, and how do you fix it?
3. When would you choose EXISTS over IN?
4. How does MySQL’s optimizer transform certain subqueries into semi-joins?
5. What is a derived table, and why does it require an alias?
6. Can you use a subquery inside an UPDATE statement? What restriction applies to self-referencing subqueries?
7. How would you find the top 3 highest-value orders per customer using a subquery?

Optimization Tips

• Prefer EXISTS/NOT EXISTS over IN/NOT IN when subquery results might contain NULLs or when checking large tables.
• Index the columns used in the correlation condition of a correlated subquery — this is often the single biggest performance lever.
• Use EXPLAIN FORMAT=JSON to check whether MySQL materializes, converts to a semi-join, or runs a dependent subquery.
• Consider rewriting correlated subqueries as window functions (ROW_NUMBER(), RANK()) for “top-N per group” style queries on MySQL 8.0+.
• Cache the result of an expensive non-correlated subquery in a variable or temporary table if it’s reused multiple times in the same session.

Summary and Key Takeaways

Subqueries let me answer layered questions in a single SQL statement instead of stitching results together manually. Scalar subqueries are great for single-value comparisons, derived tables let me pre-aggregate data before joining, and EXISTS/IN/ANY/ALL give me flexible ways to filter against a set of related rows. The biggest lesson I’ve learned is to always distinguish correlated from non-correlated subqueries, because that distinction directly predicts performance on large tables, and to default to EXISTS over IN whenever NULLs are a possibility. Once you’re comfortable nesting queries this way, a huge category of “how do I combine these two questions into one query” problems becomes almost mechanical to solve.

References

• MySQL 8.0 Reference Manual — Subqueries
• MySQL 8.0 Reference Manual — Optimizing Subqueries
• MySQL 8.0 Reference Manual — WITH (Common Table Expressions)