Subqueries are queries nested within another SQL query, often used for filtering, calculating, or transforming data in a single statement. When you use EXPLAIN on queries that include subqueries, the output reveals how the database executes both the outer query and the inner subquery. Understanding how subqueries are represented in the EXPLAIN output is crucial for interpreting and optimizing complex queries.

Subqueries can be classified as either correlated or uncorrelated, and this distinction has a direct impact on the query plan. An uncorrelated subquery is independent of the outer query—it can be executed once, and its result reused. In the previous example, the subquery inside the WHERE clause does not reference columns from the outer query, so it is uncorrelated. The EXPLAIN output shows the subquery as a separate step, often labeled as SUBQUERY in the select_type column.

A correlated subquery, on the other hand, references columns from the outer query. This means the subquery must be evaluated once for each row processed by the outer query, which can significantly affect performance. The EXPLAIN output for correlated subqueries typically shows a dependency between the subquery and the outer query, often resulting in the subquery being executed multiple times.