A correlated subquery re-executes per outer row; EXISTS/IN differ in NULL behavior and cost.

Correlation is what makes WHERE o2.cust = o.cust mean 'this row's customer' — the inner query is a per-row question, which is why it expresses per-entity logic so naturally and why EXISTS is per-row by design. Engines often decorrelate into joins under the hood, so 'correlated = always slow' is folklore — but the mental model stays per-row: that's how you read it.

SELECT
  cust,
  amt
FROM
  orders WHERE
  amt > (
    SELECT
      AVG(amt)
    FROM
      orders )
ORDER BY
  cust,
  amt;

SELECT
  cust,
  amt
FROM
  orders o
WHERE
  amt > (
    SELECT
      AVG(amt)
    FROM
      orders o2
    WHERE
      o2.cust = o.cust
  )
ORDER BY
  cust,
  amt;

The 'orders above their customer's average' report used a global average — the uncorrelated subquery computes AVG once over everything, so it missed B/15 (above B's own average but below the global); the correlated form asks per customer.

Correlated for readable per-row logic; window/pre-agg rewrites as the scale spelling; read the plan, not the folklore.