Lesson 10.4

SQL Correlated Subqueries vs Joins

A correlated subquery refers to the outer row, letting each row compare itself to a related group.

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.

Pattern

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 a JOIN. 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.

Syntax reference

Need syntax details? See the SQL subquery and EXISTS reference

Schema · Garden Shop Tables · products, categories 2 tables

Table · products

One row per product, with price, cost, and inventory levels.

9 columns · 24 rows

product_id int product_name text category_id int supplier_id int price decimal cost decimal quantity_on_hand int reorder_level int discontinued bool

Table · categories

Lookup table of product categories.

2 columns · 8 rows

category_id int category_name text

Your task

Return product_name, category_name, and price for products priced above the average price of products in the same category. Sort by category name, then price (highest first), then product name.