Last time we learned simple subqueries that run once and return results. Now meet correlated subqueries - these reference columns from the outer query and run once for each row!

They're like having a conversation between the inner and outer queries.

Engagement Message

When might you need a subquery that "knows about" the current row being processed?

Here's what makes a subquery correlated: it references a column from the outer query. The inner query can't run independently because it depends on values from each outer row.

Notice how the inner query references c.customer_id from the outer query.

Engagement Message

Why can't the inner query run by itself in this example?

The SELECT 1 deserves some explanation.

For each customer row, the subquery returns rows containing 1 - one row per matching order. If a customer has 3 orders, it returns three 1 values. If no orders exist, it returns zero rows.

EXISTS only cares whether rows were returned, not their contents.

Engagement Message

What would happen if you used SELECT 'hello' instead of SELECT 1?