The inner query mentions e.dept, a column of the outer row. Conceptually the database does this for every employee: take their department, compute that department's average, compare. For Asha it computes the Eng average (107500); for Chen the Sales average (76000). Esha is alone in Ops, equal to her own average, so > excludes her.

Two aliases of the same table appear — e outside and x inside — exactly as in a self join. They must be different, and every column should be qualified so it is obvious which copy it comes from.

The latest row per group

SELECT o.customer_id, o.id AS order_id, o.order_date, o.amount
FROM orders o
WHERE o.order_date = (
  SELECT MAX(x.order_date)
  FROM orders x
  WHERE x.customer_id = o.customer_id
)
ORDER BY o.customer_id, o.id;

For each order, the subquery finds the latest date among that customer's orders; the order is kept if it is dated on that day. This "greatest-n-per-group" pattern works for any extreme: the most expensive product per category, the first login per user, the highest score per student. Two properties to state in an interview:

• ties are kept — a customer with two orders on the latest date gets both rows. If exactly one is wanted, add a tie-breaker (AND o.id = (SELECT MAX(x.id) … )) or use ROW_NUMBER() from the window-functions module;
• customers with no orders do not appear, because there is no order row to start from.

Correlated subqueries in the select list

A correlated scalar subquery can compute a per-row value:

SELECT c.name,
       (SELECT COUNT(*) FROM orders o WHERE o.customer_id = c.id) AS orders,
       (SELECT MAX(o.order_date) FROM orders o WHERE o.customer_id = c.id) AS last_order
FROM customers c
ORDER BY c.name;

Customers with no orders get 0 and NULL — COUNT over no rows is 0, MAX is NULL — so this is an alternative to a left join with GROUP BY, and it never suffers from fan-out because each subquery aggregates its own table. The cost is that each subquery is a separate lookup per customer; for a handful of per-row figures over an indexed column that is fine, for a dozen it is better to aggregate once in a derived table (next lesson).

EXISTS is usually correlated

-- departments where someone earns more than the department's head
SELECT d.name
FROM departments d
WHERE EXISTS (
  SELECT 1 FROM employees e
  WHERE e.dept_id = d.id AND e.salary > d.head_salary
);

An uncorrelated EXISTS would be true for all rows or none, which is rarely what anyone means. When you see EXISTS, look for the outer column inside it.

How it actually runs

"Once per outer row" is the logical model, not necessarily the plan. MySQL 8:

• converts IN/EXISTS subqueries into semi joins and NOT EXISTS into antijoins;
• can turn some scalar subqueries into joins with a derived table (the subquery_to_derived optimizer switch, off by default);
• otherwise runs the subquery as a DEPENDENT SUBQUERY, once per outer row.

What makes the per-row lookups cheap is an index on the correlated column — orders(customer_id) or orders(customer_id, order_date) for the latest-order query. Without one, each evaluation scans the table, and a 100,000-row outer table means 100,000 scans. If EXPLAIN shows DEPENDENT SUBQUERY on a large table with no usable index, rewrite as a join with a derived table of per-group aggregates.

The scope trap

Name resolution inside a subquery looks in the subquery's own tables first, then in the outer query's. That rule makes correlation possible — and it means a typo can silently correlate:

-- intended: customers who have a row in the `vip` table
SELECT name FROM customers
WHERE id IN (SELECT id FROM vip);   -- but vip's column is called customer_id

vip has no column id, so the inner id resolves to customers.id. The subquery becomes "for each vip row, the outer customer's id", and id IN (id, id, …) is true for every customer whenever vip is non-empty. No error, every row returned. Qualifying the inner column (SELECT v.id FROM vip v) turns the mistake into the error it should be. Qualify every column inside a subquery — this is the single best habit for avoiding subquery bugs.

Correlated subquery or join?

Both can express most per-group comparisons. Guidelines:

• a per-row condition against an aggregate of related rows (above the department average, latest per customer) reads naturally as a correlated subquery;
• when the outer query also needs several columns from the related rows, a join is usually clearer;
• when the related table is large and the outer one too, aggregate once (derived table) and join.

Pitfalls

• Reusing the same alias inside and outside, so the "correlation" refers to the inner table itself.
• Unqualified inner columns silently binding to the outer query.
• Forgetting that latest-per-group returns ties.
• A correlated aggregate over a large table with no index on the correlated column.
• Expecting groups with no rows to appear when the outer query starts from the child table.

Key takeaways

• A correlated subquery refers to an outer column and is logically evaluated per outer row.
• It is the natural way to compare a row with an aggregate of its own group.
• col = (SELECT MAX(col) … WHERE same group) returns the latest row per group, ties included.
• Correlated scalar subqueries in the select list compute per-row counts without fan-out.
• Index the correlated column, and qualify every inner column to avoid accidental correlation.