MySQL Subqueries

Also Known As

TL;DR

Explanation

Subqueries can appear in SELECT (scalar), FROM (derived table), WHERE, or HAVING. Correlated subqueries reference the outer query and re-execute for each outer row — potentially catastrophically slow on large tables. Non-correlated subqueries execute once. IN (subquery) evaluates the full subquery; EXISTS short-circuits on first match — EXISTS is usually faster for large sets. MySQL 8+ improves subquery decorrelation automatically in some cases.

Common Misconception

Why It Matters

Common Mistakes

• Using a correlated subquery in SELECT to fetch a related value — re-executes for every row.
• Using IN with a large subquery instead of a JOIN or EXISTS.
• Nesting subqueries more than two levels deep — readability and optimisability both suffer.

Avoid When

• Avoid correlated subqueries in SELECT — replace with a JOIN.
• Avoid deep nesting — flatten with CTEs (WITH clause) in MySQL 8 for readability.

When To Use

• Use EXISTS for semi-join checks — checking whether any related row exists.
• Use derived tables in FROM for complex aggregations that need to be joined back.

Code Examples

-- Correlated: re-runs for every user row → O(n²)
SELECT email,
       (SELECT COUNT(*) FROM orders o WHERE o.user_id = u.id) AS cnt
FROM users u;

-- EXISTS short-circuits on first match
SELECT * FROM users u
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id AND o.status = 'paid');

-- Derived table in FROM (executes once)
SELECT u.email, stats.total
FROM users u
JOIN (SELECT user_id, SUM(total) AS total FROM orders GROUP BY user_id) stats
    ON stats.user_id = u.id;

References

• ↗ https://dev.mysql.com/doc/refman/8.0/en/subqueries.html

Tags

Related Terms