A non-correlated subquery runs once and stands alone, but a correlated subquery is different: it references a column from the outer query, so it must be re-evaluated for each outer row. This lets you ask per-row questions, such as whether each player scored above their own team's average, where the comparison value changes from row to row depending on that row's context.
EXISTS is the natural partner of correlated subqueries. Rather than returning values, EXISTS simply tests whether the inner query produces any rows at all for the current outer row, returning true or false. This makes it perfect for presence and absence questions, like "players who have at least one century" or "teams with no away matches", expressed efficiently without pulling any inner data.
Correlated subqueries and EXISTS unlock genuinely relational questions that depend on a relationship between the outer row and a set of related rows. They are more powerful than simple subqueries but require care, because the per-row evaluation has performance implications and the handling of NULLs in NOT IN versus NOT EXISTS is a notorious source of silent bugs that this lesson will make clear.