Correlated vs Non-Correlated Subqueries in SELECT
What you'll learn: You'll understand the critical distinction between subqueries that depend on the outer query and those that run independently.
The Core Difference
When you place a subquery in the SELECT clause, it can work in two fundamentally different ways:
Non-correlated subqueries are completely independent. They execute once, return a result, and that same result is used for every row in the outer query. Think of it like looking up today's exchange rate once and applying it to every transaction.
Correlated subqueries reference columns from the outer query. They must re-execute for each row of the outer query, because their logic depends on that specific row's values. This is like calculating a custom discount for each customer based on their individual purchase history.
How to Spot the Difference
-- Non-correlated: runs once total
SELECT
product_name,
price,
(SELECT AVG(price) FROM products) AS avg_price
FROM products;
The subquery (SELECT AVG(price) FROM products) doesn't reference anything from the outer query—it calculates the overall average once.
-- Correlated: runs once per row
SELECT
p.product_name,
p.price,
(SELECT AVG(price)
FROM products p2
WHERE p2.category = p.category) AS category_avg
FROM products p;
Notice WHERE p2.category = p.category—this subquery references p.category from the outer query. For each product, it recalculates the average for that specific category.
Performance Impact
Non-correlated subqueries are generally faster since they execute once. Correlated subqueries can be slower because they run repeatedly, though modern databases often optimize them intelligently.
Key Takeaway: Non-correlated subqueries in SELECT are independent and execute once; correlated subqueries reference outer query columns and execute once per row, making them more flexible but potentially slower.