Subqueries

Intermediate

Joins & Set Ops

Scalar, row, and table subqueries appear in SELECT, FROM, and WHERE; correlated subqueries reference the outer query and execute per row — often replaceable with JOIN for better performance.

Overview

A subquery is a SELECT statement nested inside another query. MySQL supports scalar subqueries (return one value), row subqueries (return one row), table subqueries (return a result set as a derived table), and correlated subqueries (reference the outer query and re-execute per outer row). Correlated subqueries are convenient but dangerous at scale — they run once per outer row, turning an O(n) query into O(n²). The MySQL optimizer can sometimes de-correlate them automatically, but you should verify with EXPLAIN and rewrite as a JOIN or CTE when the plan shows "Select tables optimized away" is absent.

Scalar, IN, and EXISTS subqueries

Scalar subqueries return a single value for use in SELECT or WHERE. EXISTS is usually more efficient than IN for large subquery results.

SQL — scalar, IN, and EXISTS subqueries

-- Scalar subquery in SELECT (executes once per output row)
SELECT
    o.id,
    o.total,
    (SELECT AVG(total) FROM orders) AS avg_order_total,
    o.total - (SELECT AVG(total) FROM orders) AS diff_from_avg
FROM orders o;

-- IN subquery: customers who placed an order this month
SELECT id, email
FROM customers
WHERE id IN (
    SELECT DISTINCT customer_id
    FROM orders
    WHERE created_at >= DATE_FORMAT(NOW(), '%Y-%m-01')
);

-- EXISTS (often faster than IN for large sets — stops at first match)
SELECT id, email
FROM customers c
WHERE EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.id          -- correlated reference
      AND o.created_at >= '2024-01-01'
);
-- Note: EXISTS uses index on orders.customer_id — check EXPLAIN

-- NOT EXISTS — customers with NO orders (better than LEFT JOIN IS NULL for large tables)
SELECT id, email
FROM customers c
WHERE NOT EXISTS (
    SELECT 1 FROM orders o WHERE o.customer_id = c.id
);

Derived tables (table subqueries in FROM)

A subquery in the FROM clause creates a derived table. MySQL materialises it into a temporary table; adding an alias is required. CTEs (MySQL 8.0+) are the cleaner alternative.

SQL — derived table, CTE equivalent, LATERAL join

-- Derived table: average order value per customer
SELECT
    c.email,
    customer_stats.order_count,
    customer_stats.total_spend
FROM customers c
JOIN (
    SELECT
        customer_id,
        COUNT(*)      AS order_count,
        SUM(total)    AS total_spend
    FROM orders
    GROUP BY customer_id
) AS customer_stats ON c.id = customer_stats.customer_id
WHERE customer_stats.total_spend > 1000
ORDER BY customer_stats.total_spend DESC;

-- Equivalent CTE (MySQL 8.0+) — same execution plan, cleaner syntax
WITH customer_stats AS (
    SELECT customer_id, COUNT(*) order_count, SUM(total) total_spend
    FROM orders GROUP BY customer_id
)
SELECT c.email, cs.order_count, cs.total_spend
FROM customers c
JOIN customer_stats cs ON c.id = cs.customer_id
WHERE cs.total_spend > 1000;

-- Derived table with LATERAL (MySQL 8.0.14+): can reference outer table
SELECT c.id, c.email, latest.created_at AS last_order_date
FROM customers c
JOIN LATERAL (
    SELECT created_at FROM orders
    WHERE customer_id = c.id
    ORDER BY created_at DESC LIMIT 1
) AS latest ON TRUE;

Correlated subquery vs JOIN performance

Correlated subqueries execute once per outer row — O(n²) for large tables. EXPLAIN shows "Select tables optimized away" only if MySQL de-correlates them. Rewrite as JOIN or CTE when not.

SQL — correlated subquery vs JOIN rewrite

-- SLOW: correlated subquery executes once per order row
SELECT o.id, o.total,
  (SELECT c.email FROM customers c WHERE c.id = o.customer_id) AS customer_email
FROM orders o;
-- EXPLAIN: "Select tables optimized away" absent → runs N times

-- FAST: JOIN executes once, uses index
SELECT o.id, o.total, c.email AS customer_email
FROM orders o
JOIN customers c ON o.customer_id = c.id;
-- EXPLAIN: type=ref, key=idx_customer_id

-- SLOW: correlated EXISTS with sequential scan
SELECT * FROM orders o
WHERE (SELECT COUNT(*) FROM items i WHERE i.order_id = o.id) > 3;

-- FAST: aggregate in derived table then filter
SELECT o.*
FROM orders o
JOIN (
    SELECT order_id, COUNT(*) AS item_count
    FROM items
    GROUP BY order_id
    HAVING COUNT(*) > 3
) AS heavy_orders ON o.id = heavy_orders.order_id;

Key Points to Remember

1Correlated subqueries execute once per outer row — avoid in WHERE/SELECT on large tables; rewrite as JOIN.
2EXISTS stops at the first matching row; IN fetches all matching values — EXISTS is usually faster for large subsets.
3Derived tables in FROM must have an alias; MySQL may materialise them into a temp table.
4MySQL 8.0 CTEs (WITH clause) are syntactically cleaner than derived tables with the same execution plan.
5LATERAL joins (MySQL 8.0.14+) allow derived tables to reference outer query columns — useful for "latest N per group".
6Always check EXPLAIN for correlated subqueries — look for "dependent subquery" in Extra which confirms O(n²) execution.

Interview Questions

What is the difference between a correlated and a non-correlated subquery?

EasyAmazon

Why is EXISTS usually faster than IN for large subquery result sets?

MediumGoogle

How would you rewrite a correlated subquery in SELECT to avoid N+1 query execution?

MediumNetflix

What is a LATERAL join and what problem does it solve that a regular derived table cannot?

HardUber

How do you identify a correlated subquery in an EXPLAIN output?

MediumMeta

Ask Aria about Subqueries

Your personal AI tutor — ask anything about this concept

Revision Status

Personal Notes

Discussion

Loading discussion…

JOINs — INNER, LEFT, RIGHT, CROSS

GROUP BY & Aggregation