Correlated Subqueries in SQL with Examples

6
0

Introduction

A shop wants to find products that cost more than the average price of their own category. Comparing every product with the average price of the entire catalog would answer a different question.

A correlated subquery lets an inner query use a value from the current row of an outer query. Here, that value is the product’s category, which determines which prices belong in the comparison.

What Is a Correlated Subquery?

A correlated subquery is a query nested inside another SQL query that references a column from the outer query.

The outer query supplies a value, and the inner query uses it to calculate a result or check a condition for that row. Because of this dependency, the inner query cannot run unchanged on its own.

The logic can be understood as a row-by-row process: taking an outer row, evaluating the inner query using that row’s values, and using the result in the outer query.

Example: Products Above Their Category Average

Consider this products table. Each product has a unique product_id, and the name, category, and price are not NULL. All prices use the same currency.

product_id

product_name

category

price

1

SQL Basics

Books

200.00

2

SQL Guide

Books

400.00

3

Chess Set

Games

600.00

4

Puzzle Box

Games

900.00

5

Strategy Game

Games

1200.00

Goal: Find products whose price is greater than the average price of their own category.

We use p for the product being checked by the outer query and p2 for the products considered inside the subquery. These aliases distinguish two uses of the same table; they do not create separate tables.

SELECT
p.product_id,
p.product_name,
p.category,
p.price
FROM products AS p
WHERE p.price > (
SELECT AVG(p2.price)
FROM products AS p2
WHERE p2.category = p.category
)
ORDER BY p.product_id;

Output

product_id

product_name

category

price

2

SQL Guide

Books

400.00

5

Strategy Game

Games

1200.00

The Books average is (200 + 400) / 2 = 300, so SQL Guide qualifies. The Games average is (600 + 900 + 1200) / 3 = 900, so Strategy Game qualifies.

Puzzle Box is excluded because its price equals the Games average. The condition uses >, so equality does not qualify. SQL Basics and Chess Set are below their category averages.

How the Correlation Works

The link is p2.category = p.category. The left side belongs to the inner query; the right side refers to the current outer product.

For SQL Guide, the inner query considers the Books prices. For Puzzle Box, it considers the Games prices. The diagram follows these two cases to show how the outer row determines the inner query’s matching rows.

Correlated Subquery Flow

Correlated Subquery Flow

The average includes the current product itself because it also belongs to the matching category. Products in the same category therefore use the same average.

Here, AVG without GROUP BY produces one value for the comparison. A subquery used as a single value is called a scalar subquery. Correlation describes its dependency on the outer query; scalar describes the shape of its result.

Correlated vs. Uncorrelated Subqueries

An uncorrelated subquery does not reference the outer query. Its result can be determined independently.

Feature

Uncorrelated subquery

Correlated subquery

Outer reference

Does not reference the outer query

References a column from the outer query

Run independently

Can run on its own

Needs the outer values or a rewrite

Example question

Is this price above the catalog average?

Is this price above its category average?

Comparison in this dataset

Every product uses 660.00

Books use 300.00; Games use 900.00

Using the same table inside and outside a query does not automatically make the subquery correlated. The defining feature is the reference to an outer column.

Correlation also works with other kinds of subqueries. A correlated subquery in SELECT can show a related maximum or count beside each outer row. With EXISTS or NOT EXISTS, it can check whether matching related rows exist or are absent.

Common Mistakes and Performance

  • Omitting the correlation condition: Removing p2.category = p.category changes the inner query to calculate the catalog average, which is 660.00. The query would then return Puzzle Box and Strategy Game, answering a different question.

  • Confusing the aliases: A condition such as p2.category = p2.category compares an inner value with itself. It does not connect the inner query to the outer product. Qualify columns with the intended alias.

  • Returning several rows where one value is expected: An ordinary comparison such as price > (subquery) needs a single-column result containing at most one row. Returning several prices causes an error. AVG without GROUP BY gives this example one aggregate result.

  • Overlooking NULL values: AVG ignores NULL prices. If it has no non-NULL values to average, it returns NULL, and a greater-than comparison with that result does not select the row. The example avoids this case by requiring non-NULL prices.

Correlated subqueries can be expensive when the chosen plan repeatedly scans many inner rows. An index on the correlation column, such as category, may help, but its benefit depends on the data and the execution plan.

The number of outer rows does not by itself tell how many times the database physically executes the subquery. Optimizers can transform some queries, and an equivalent JOIN is not automatically faster. For large datasets, inspect the execution plan with EXPLAIN and compare performance using realistic data.

Summary

A correlated subquery references a column from an outer query, allowing its result or condition to depend on the current outer row. Clear aliases and the correct correlation condition make that dependency easy to follow. Use it for questions about related rows, such as comparing each product with its category average, and assess performance from the database’s execution plan.

CS Core

Read Similar Blogs

Comments0