Lesson 6 of 12 · 22 min
Correlated subqueries and what they cost
By the end of this lesson you can write correlated scalar subqueries in WHERE and SELECT, trace how they are evaluated row by row, and recognise when to replace them with a pre-aggregated join.
In lesson 1 the subquery (SELECT AVG(unit_price) FROM products) gave one number for the whole query. Now the buyer asks a sharper question: which products are expensive for their category? A Najm phone at SAR 1,899 is cheap next to a laptop and very expensive next to a coffee capsule pack. Each product needs its own comparison value: the average of its category.
A correlated subquery refers to a column of the outer query. Conceptually, PostgreSQL evaluates it once per outer row, plugging in that row's values.
This lesson, its 4 graded exercises and every other Members lesson in SQL Quest come with a Formula Quest membership that includes SQL (Single Quest with SQL selected, Quest Trio including SQL, or Quest Unlimited), or with SQL access from your organization.
Free to everyone: all of SQL Foundations and Retrieving and Filtering Data, the first lesson of every track, the playground, the dataset library and the SQL reference. See free lessons