Subquery

In MySQL, a subquery (also called a nested query or inner query) is a SELECT statement written inside another SQL statement, such as SELECT, INSERT, UPDATE, or DELETE. Subqueries allow you to use the result of one query inside the logic of another, which is very useful for complex filtering, comparisons, and calculations.

With subqueries, you can answer questions like:

• “Which customers have orders above the average order value?”

• “Which employees earn more than the average salary in their department?”

• “Which products are in categories that are going to be discontinued?”

All of these can be expressed as single SQL statements using subqueries, instead of multiple separate queries from the application.

What Is a Subquery?

A subquery is a SELECT wrapped in parentheses that appears inside another SQL statement. The outer statement is usually called the main query or outer query.

Basic Syntax Pattern

For example:

Here:

• The inner query computes AVG(salary) from the employees table.

• The outer query compares each employee’s salary to that average and returns only those who earn more.

The subquery is evaluated first, and its result is plugged into the outer query’s condition.

Where Subqueries Can Appear

Subqueries can appear in several parts of a SQL statement:

• In the WHERE clause:

  • Used for filtering rows based on values or sets computed by the subquery.

• Subqueries can be used to filter groups dynamically.

Types of Subqueries

1. Scalar Subquery

A scalar subquery returns exactly one value (one row, one column), and is typically used with comparison operators such as =, >, <, >=, <=, or <>.

Example:

Here, the inner query produces a single number, and the outer query compares each row’s salary against that value.

2. Row Subquery

A row‑style subquery returns one row (multiple columns), often used when comparing composite keys or tuples.

This returns employees whose name and department match exactly those in the fired_employees subquery.

3. Table‑Style (Set‑Style) Subquery

A table‑style subquery returns a result set (multiple rows and/or columns) and is commonly used with operators such as IN, EXISTS, ANY, or ALL.

Example with IN:

Example with EXISTS:

Here, the subquery checks if any matching row exists, without returning the actual data.

Correlated Subqueries

A correlated subquery is one in which the inner query references columns from the outer query. Because of this dependency, the inner query may be re‑evaluated for each row of the outer query, which can affect performance.

Example:

In this case, for each row (outerr), the inner subquery computes the average salary within that row’s department and compares the current employee’s salary to it.

Correlated subqueries are powerful for per‑group or per‑entity logic, but should be used carefully on large tables because they can be slower if not supported by proper indexing.

Common Use Patterns

Filter Rows Using IN and EXISTS

• IN example:
  Find employees whose department is in a specific list:

• EXISTS example:
  Find customers who have at least one order:

Compare to Aggregate Values

A subquery is often used to compute summary values (average, maximum, minimum, total) and then compare individual rows against them.

This returns all employees whose salary is above the overall average.

Subquery as a Derived Table

When a subquery is used in the FROM clause, it becomes a derived table (also called an inline view). This is useful for pre‑processing or aggregating data before joining.

Here, the derived table dept_stats holds department‑level sums and averages, which are then joined with the main employees table.

When to Use Subqueries

• Complex filtering based on dynamic sets:

  • “Show orders where the customer is in a specific group” is naturally expressed as a subquery.

• Hierarchical or self‑reference logic:

  • “Employees who earn more than their manager” or “Categories whose parent is discontinued.”

• Computed thresholds:

  • Comparing rows against averages, totals, or maximums computed over the same or related data.

Performance and Best Practices

While subqueries are powerful, they can hurt performance if used poorly:

• Index key columns:

  • Columns used in correlation or in the WHERE of the subquery should be indexed.

• Avoid unnecessary correlated subqueries over large tables:

  • If possible, rewrite using JOIN and GROUP BY for better optimization.

• Use EXISTS vs. IN for large sets:

  • EXISTS often performs better than IN when the inner query returns many rows, because it stops as soon as one match is found.

• Test execution plans:

  • Use EXPLAIN to see how MySQL executes the correlated and non‑correlated subqueries.

Subquery vs. JOIN: A Quick Comparison

In practice, subqueries and joins are often used together, depending on which expresses the intent more clearly.

Summary

A subquery in MySQL is a SELECT statement embedded inside another SQL statement, allowing you to compute values or sets dynamically and use them directly in filtering, expressions, or table‑like contexts. Subqueries support scalar, row, and table‑style patterns, along with correlated subqueries for per‑row logic. When used with proper indexing and awareness of performance, subqueries become a vital tool for writing advanced, expressive SQL that handles complex business rules inside the database.