SQL Correlated Subqueries vs Joins

A correlated subquery uses a value from the outer query. The database evaluates the inner query in the context of each outer row. That sounds abstract, but the use case is practical: compare each product to the average price for its own category.

This lesson also keeps a regular JOIN in the outer query. The join adds category names to the final output; the subquery handles the row-specific comparison.

select outer_table.item_name, outer_table.metric
from outer_table
where outer_table.metric > (
  select avg(inner_table.metric)
  from inner_table
  where inner_table.group_id = outer_table.group_id
)

When a join is still better

If you need columns from another table in the final result, use aJOIN. If you need a named intermediate result, use a CTE. A correlated subquery is strongest when the question is about existence or a row-by-row comparison value.