MySQL Subqueries: Types and Examples

What a subquery returns

A subquery is a SELECT nested inside another SQL statement. Depending on where it appears, it can return a single scalar value, a column of values, or a table-shaped result. Subqueries let a query express a dependency: find products whose price is above an average, or customers for whom a related order exists. The outer query consumes the inner result as part of its own filter or source.

A scalar subquery must return one value in its context. For example, comparing each product price to the average price uses an aggregate subquery with one result row. If a scalar subquery unexpectedly returns multiple rows, MySQL reports an error. Add a uniqueness condition, an aggregate, or a deterministic limit only when that matches the actual requirement; LIMIT 1 without a meaningful order can hide ambiguous data rather than solve it.

SELECT product_id, name, price
FROM products
WHERE price > (
  SELECT AVG(price)
  FROM products
);

The inner query computes a single average, and the outer query returns rows above it. NULL prices are ignored by AVG, so nullable price semantics affect the comparison. If no non-NULL prices exist, AVG returns NULL and no comparison is true. Schema rules and the report's intended behavior should make this case explicit.

IN and EXISTS for related sets

A subquery can provide a set for IN, such as finding products whose identifiers appear in active order items. For existence logic, EXISTS asks whether a matching row is present; its selected values are normally irrelevant, so SELECT 1 is conventional. A correlated subquery references a column from the outer query. MySQL optimizes many such patterns, but execution cost depends on the query and data. Do not assume a correlated query literally runs as a slow independent scan for each row, and do not assume the optimizer always transforms every shape optimally.

SELECT c.customer_id, c.email
FROM customers AS c
WHERE EXISTS (
  SELECT 1
  FROM orders AS o
  WHERE o.customer_id = c.customer_id
    AND o.status = 'paid'
);

The condition returns each customer once if at least one paid order exists, regardless of how many such orders the customer has. A join would return one customer row per matching order unless it is grouped or deduplicated. Use EXISTS when the question is about existence, not when columns from every related row are needed.

NOT IN, NULL, and anti-joins

NOT IN can be surprising if its value list or subquery contains NULL. Comparisons against NULL evaluate to UNKNOWN, and WHERE retains only TRUE rows. This can cause a NOT IN query to return no rows even when many values appear unmatched. NOT EXISTS is often safer for finding rows with no related match, provided the correlation condition correctly identifies the relationship. Test nullable keys, empty subquery results, and duplicate values.

A LEFT JOIN with an IS NULL check is another anti-join pattern. Use a right-side column that must be non-NULL for a matched record, such as a primary key. The best form depends on intent, indexes, and readability; compare query plans rather than relying on folklore that one syntax is always faster.

Derived tables and query structure

A subquery in FROM produces a derived table that the outer query can join or filter. It is useful for separating stages, such as calculating order totals by customer and then joining those totals to customer names. Give the derived table an alias in MySQL. For a named intermediate result used in a single statement, a CTE can make a multi-stage query more readable; see MySQL CTEs.

Subqueries should make the data question clearer, not hide a poorly understood join or duplicate a business rule across many queries. Compare the structure with an explicit join. When aggregates are involved, ensure the inner query groups at the correct level so outer joins do not multiply summarized values.

Performance and debugging

Check the execution plan with EXPLAIN, verify indexes on correlation and filter columns, and test with realistic data distributions. A selective inner predicate may perform well; an unindexed correlated condition over large tables may be costly. Run the inner query by itself when possible to inspect its rows and types, then add the outer query. Compare estimated rows and, where supported and safe, actual execution information. Be careful with EXPLAIN ANALYZE because it executes the statement.

Choose scalar, set, EXISTS, or derived-table forms based on the shape of the question. Account for NULLs and cardinality, then validate the plan rather than assuming subqueries or joins have a universal performance ranking. Continue with joins, NULL behavior, and EXPLAIN.