Subqueries — MySQL

Subqueries in MySQL

A subquery is a SELECT statement nested inside another SQL statement. Subqueries let you break complex problems into smaller steps, use aggregate results as filter criteria, and derive temporary result sets without creating permanent tables. Understanding when to use subqueries — and when a JOIN is better — is one of the most valuable SQL skills you can develop.

Types of Subqueries

Type	Returns	Used in
Scalar	Exactly one row, one column	SELECT list, WHERE, HAVING
Row	One row, multiple columns	WHERE with row constructors
Table (derived)	Multiple rows and columns	FROM clause (must be aliased)
Correlated	One value per outer row	WHERE, SELECT — references outer query

Scalar Subqueries

A scalar subquery returns exactly one row and one column. It can appear anywhere a single value is expected — in the SELECT list, in WHERE, or in HAVING.

SQL

-- Scalar subquery in SELECT: add avg order value to every row
SELECT
  order_id,
  customer_id,
  total,
  ROUND((SELECT AVG(total) FROM orders WHERE status = 'delivered'), 2) AS avg_total,
  total - (SELECT AVG(total) FROM orders WHERE status = 'delivered')   AS diff_from_avg
FROM orders
WHERE status = 'delivered'
ORDER BY diff_from_avg DESC;

-- Scalar subquery in WHERE
SELECT order_id, customer_id, total
FROM orders
WHERE total > (SELECT AVG(total) FROM orders WHERE status = 'delivered')
  AND status = 'delivered'
ORDER BY total DESC;

Note

Scalar subqueries that return more than one row cause a runtime error: "Subquery returns more than 1 row". If there is any chance the subquery could return multiple rows, use IN, EXISTS, or a JOIN instead.

Row Subqueries

SQL

-- Find products where (category_id, price) matches a known pair
SELECT product_id, name, category_id, price
FROM products
WHERE (category_id, price) = (
  SELECT category_id, MAX(price)
  FROM products
  WHERE category_id = 3
);

-- Find employees with the same (department, job_title) as a specific employee
SELECT employee_id, full_name, department, job_title
FROM employees
WHERE (department, job_title) = (
  SELECT department, job_title
  FROM employees
  WHERE employee_id = 42
)
AND employee_id <> 42;

Table Subqueries — Derived Tables

A subquery in the FROM clause produces a derived table. It must be given an alias. Derived tables let you apply a WHERE filter to aggregated results (which you cannot do directly).

SQL

-- Find customers who are in the top 10% of spenders
SELECT *
FROM (
  SELECT
    customer_id,
    ROUND(SUM(total), 2) AS lifetime_value,
    NTILE(10) OVER (ORDER BY SUM(total) DESC) AS decile
  FROM orders
  WHERE status = 'delivered'
  GROUP BY customer_id
) AS customer_stats
WHERE decile = 1
ORDER BY lifetime_value DESC;

-- Derived table to filter aggregated results
SELECT region_stats.*
FROM (
  SELECT
    c.country,
    COUNT(DISTINCT c.customer_id)       AS customers,
    ROUND(SUM(o.total), 2)              AS revenue
  FROM customers AS c
  JOIN orders    AS o ON c.customer_id = o.customer_id
  WHERE o.status = 'delivered'
  GROUP BY c.country
) AS region_stats
WHERE revenue > 10000
ORDER BY revenue DESC;

Subqueries in WHERE — IN and NOT IN

SQL

-- Customers who have placed at least one order
SELECT customer_id, first_name, email
FROM customers
WHERE customer_id IN (
  SELECT DISTINCT customer_id FROM orders
);

-- Customers who have NEVER placed an order
SELECT customer_id, first_name, email
FROM customers
WHERE customer_id NOT IN (
  SELECT customer_id FROM orders WHERE customer_id IS NOT NULL
);

-- Products in orders placed by VIP customers (top 100 spenders)
SELECT DISTINCT p.product_id, p.name
FROM products AS p
WHERE p.product_id IN (
  SELECT oi.product_id
  FROM order_items AS oi
  WHERE oi.order_id IN (
    SELECT order_id FROM orders
    WHERE customer_id IN (
      SELECT customer_id FROM orders
      WHERE status = 'delivered'
      GROUP BY customer_id
      ORDER BY SUM(total) DESC
      LIMIT 100
    )
  )
);

Warning

When using NOT IN with a subquery, ensure the subquery cannot return NULL values. If it does, the entire NOT IN condition returns no rows. Filter NULLs with WHERE col IS NOT NULL inside the subquery, or use NOT EXISTS instead.

Correlated Subqueries

A correlated subquery references a column from the outer query. It re-executes once for each row in the outer query. This makes them powerful but potentially slow on large tables.

SQL

-- For each order, show how it compares to that customer's average
SELECT
  o.order_id,
  o.customer_id,
  o.total,
  ROUND((
    SELECT AVG(o2.total)
    FROM orders AS o2
    WHERE o2.customer_id = o.customer_id   -- correlated: references outer o.customer_id
      AND o2.status = 'delivered'
  ), 2) AS customer_avg_order
FROM orders AS o
WHERE o.status = 'delivered'
ORDER BY o.customer_id, o.total;

-- Find the most expensive product in each category (correlated)
SELECT p1.product_id, p1.name, p1.category_id, p1.price
FROM products AS p1
WHERE p1.price = (
  SELECT MAX(p2.price)
  FROM products AS p2
  WHERE p2.category_id = p1.category_id   -- correlated
    AND p2.is_active = 1
)
AND p1.is_active = 1
ORDER BY p1.category_id;

Tip

Correlated subqueries execute once per row in the outer query. For large outer result sets this is slow. Rewrite using a JOIN to a derived table or a window function for better performance.

EXISTS — Checking Row Existence

EXISTS tests whether a subquery returns at least one row. It short-circuits as soon as the first matching row is found, making it very efficient for existence checks.

SQL

-- Customers who have placed at least one order (EXISTS version)
SELECT customer_id, first_name, email
FROM customers AS c
WHERE EXISTS (
  SELECT 1
  FROM orders AS o
  WHERE o.customer_id = c.customer_id
);

-- Note: SELECT 1 is conventional — the actual column list doesn't matter
-- EXISTS only cares whether at least one row is returned

-- Customers who have a delivered order over $200
SELECT c.customer_id, c.first_name
FROM customers AS c
WHERE EXISTS (
  SELECT 1
  FROM orders AS o
  WHERE o.customer_id = c.customer_id
    AND o.status = 'delivered'
    AND o.total > 200
);

NOT EXISTS — Anti-Join Pattern

NOT EXISTS is the NULL-safe alternative to NOT IN with a subquery. It returns rows from the outer query where the subquery matches zero rows — and unlike NOT IN, it handles NULLs correctly.

SQL

-- Customers who have NEVER placed an order (NOT EXISTS — NULL safe)
SELECT customer_id, first_name, email
FROM customers AS c
WHERE NOT EXISTS (
  SELECT 1
  FROM orders AS o
  WHERE o.customer_id = c.customer_id
);

-- Products never added to any wishlist
SELECT product_id, name, price
FROM products AS p
WHERE NOT EXISTS (
  SELECT 1
  FROM wishlist_items AS w
  WHERE w.product_id = p.product_id
)
AND is_active = 1;

EXISTS vs IN — Performance Comparison

Aspect	IN (subquery)	EXISTS
Evaluation	Materializes subquery result first	Short-circuits on first match
NULL handling	Unsafe — NULL in list causes issues	NULL-safe
Best for	Small subquery result sets	Large outer tables, existence checks
Readable?	Very readable for simple cases	Slightly more verbose

Subqueries in HAVING

SQL

-- Find categories whose average price is above the overall average
SELECT
  category_id,
  ROUND(AVG(price), 2) AS avg_price
FROM products
WHERE is_active = 1
GROUP BY category_id
HAVING AVG(price) > (
  SELECT AVG(price) FROM products WHERE is_active = 1
)
ORDER BY avg_price DESC;

Subquery Optimization — When to Use a JOIN Instead

Subqueries are often rewritable as JOINs. JOINs can be faster because the optimizer has more freedom to choose the best execution plan.

SQL

-- Subquery version: customers with at least one order (slower for large tables)
SELECT customer_id, first_name
FROM customers
WHERE customer_id IN (SELECT DISTINCT customer_id FROM orders);

-- JOIN version: equivalent, often faster
SELECT DISTINCT c.customer_id, c.first_name
FROM customers AS c
JOIN orders    AS o ON c.customer_id = o.customer_id;

-- Correlated subquery: most expensive product per category (slow)
SELECT p1.name, p1.category_id, p1.price
FROM products AS p1
WHERE p1.price = (SELECT MAX(p2.price) FROM products AS p2 WHERE p2.category_id = p1.category_id);

-- JOIN version: same result, better performance
SELECT p.name, p.category_id, p.price
FROM products AS p
JOIN (
  SELECT category_id, MAX(price) AS max_price
  FROM products
  GROUP BY category_id
) AS cat_max ON p.category_id = cat_max.category_id AND p.price = cat_max.max_price;

Subquery Limitations in MySQL

Subqueries in FROM (derived tables) cannot reference other tables in the same FROM clause
In MySQL 5.7 and earlier, subqueries in FROM are not automatically cached — repeated execution can be slow
MySQL 8.0+ materializes derived tables and can use indexes on them
LIMIT inside a subquery used with IN is not supported in MySQL 5.x
Very deeply nested subqueries can confuse the optimizer — flatten with CTEs when possible

Tip

For complex subquery logic that you need to reference multiple times, use a CTE (WITH clause) instead. CTEs are named, reusable within the query, and much easier to read and debug than nested subqueries.