Why can EXISTS filter on a related table without returning any of its columns?

## Predicate versus table reference SQL has two distinct ways a subquery can appear. In `FROM`, a subquery is a **table reference**: it contributes rows and columns, and you name it so the outer query can project from it. In `WHERE`, inside `EXISTS`, a subquery is an argument to a **predicate**: the whole expression evaluates to TRUE or FALSE for the row currently being tested, and nothing it selects becomes part of the result. That distinction explains the scoping rule. Correlation names flow *inward*: the subquery inside `EXISTS` can see `c` from the enclosing `FROM` clause, which is what makes the correlation possible. They do not flow *outward*: `o`, declared inside the subquery, ceases to exist the moment the subquery's closing parenthesis is reached. So this is not a runtime surprise but a compile-time error: ```sql -- invalid: o is not in scope in the select list SELECT c.name, o.total FROM customers c WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id); ``` ## The semi-join contract Stated positively, a semi-join has two guarantees worth memorising: 1. **Projection**: every output column comes from the left input. 2. **Cardinality**: every output row corresponds to exactly one left row, and no left row appears twice. Those guarantees are the reason `EXISTS` and `IN` are duplicate-free while a join is not, and they are also the reason `EXISTS` cannot hand you the matched row's data. You cannot get both properties at once: the moment the result must carry a value from the right side, either you commit to one output row per match (a join) or you commit to choosing exactly one right-side value per left row. ## What to write when you need the matched data The useful move in an interview is to notice that the requirement has become ambiguous and to say so. "Customers who have ordered" is well defined; "customers who have ordered, with the order total" is not — which order? **One derived value per outer row: correlated scalar subquery.** ```sql SELECT c.id, c.name, (SELECT MAX(o.order_date) FROM orders o WHERE o.customer_id = c.id) AS last_order_date FROM customers c WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id); ``` A scalar subquery must produce at most one row; an aggregate guarantees that. It keeps the grain at one row per customer. The cost is that each additional column needs its own subquery. **Several derived values: join to a pre-aggregated derived table.** ```sql SELECT c.id, c.name, s.order_count, s.last_order_date FROM customers c JOIN (SELECT customer_id, COUNT(*) AS order_count, MAX(order_date) AS last_order_date FROM orders GROUP BY customer_id) s ON s.customer_id = c.id; ``` Because the derived table has one row per `customer_id`, the join cannot fan out, and the inner join also performs the existence filter for free — customers with no orders have no row in `s`. This is the standard rewrite when three or four order-derived columns are wanted. **One row per match: just join.** If the answer really is "list every order with its customer's name", the repetition of the customer name is correct output, not a defect, and no semi-join is involved. **One whole matching row per left row.** "The most recent order's row for each customer" is a top-N-per-group requirement, solved with ranking or a lateral derived table rather than with `EXISTS` — a different pattern with its own rules. ## Related trap: EXISTS with no correlation `WHERE EXISTS (SELECT 1 FROM orders)` is legal but almost never what was meant: the subquery does not mention the outer row, so the predicate has the same value for every customer. Either all customers pass or none do. If you write `EXISTS` and the subquery contains no reference to the outer query, you have written a global switch, not a filter. ## Summary `EXISTS` answers exactly one question — "is there at least one such row?" — and answers it about the row in front of it. Its power is that it filters without disturbing the shape of the result; its limit is that it tells you nothing about the row it found. Recognising which of those two you need is the actual skill being tested.