What does an EXISTS subquery predicate evaluate to, and can it ever be UNKNOWN?

## The predicate in one sentence `EXISTS (<subquery>)` is a *predicate*: it produces a truth value, not a data value. It is TRUE when the subquery produces at least one row and FALSE when the subquery produces zero rows. That is the entire definition, and everything else about EXISTS follows from it. ```sql SELECT c.id, c.name FROM customers c WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id); ``` For each candidate customer row, the inner query is conceptually asked "are there any order rows for this customer?" and the answer — yes or no — decides whether the customer row survives. ## Why "two-valued" is the point SQL predicates normally live in three-valued logic: `TRUE`, `FALSE`, and `UNKNOWN`. A comparison yields UNKNOWN whenever an operand is NULL, because NULL means "value unknown" and you cannot say whether an unknown value equals 5. `WHERE` keeps only rows whose predicate is TRUE, so UNKNOWN silently discards rows. EXISTS escapes that entirely. It does not compare anything at the top level; it counts. A subquery either produced rows or it did not, and there is no third possibility. So `EXISTS` yields only TRUE or FALSE, and `NOT EXISTS` is its exact negation with no gap in the middle. This totality is the reason `NOT EXISTS` is recommended for "there is no matching row" filters while `NOT IN` over a nullable column is a hazard: NULLs inside the subquery can make `NOT IN` evaluate to UNKNOWN, but they cannot make `NOT EXISTS` do anything except answer yes or no. A row full of NULLs is still a row: ```sql -- TRUE: the subquery returned one row, even though its only column is NULL SELECT 'yes' WHERE EXISTS (SELECT NULL); ``` Note the difference in level. NULLs *inside* an EXISTS subquery can still affect which rows the inner `WHERE` keeps — an inner predicate that evaluates to UNKNOWN drops that inner row. What NULL cannot do is make the EXISTS predicate itself undecided. If every inner row is dropped, EXISTS is simply FALSE. ## The select list is not evaluated Because only row existence matters, the columns a subquery projects inside EXISTS are irrelevant to the result. `EXISTS (SELECT 1 ...)`, `EXISTS (SELECT * ...)` and `EXISTS (SELECT o.id ...)` all mean the same thing. `SELECT 1` is the common convention because it makes the intent obvious to a human reader: nothing is being returned, only tested. Candidates sometimes claim `SELECT *` is wrong or slow; the standard treats the list as unused, so the choice is a style question, not a semantic one. By the same reasoning, decorating the inner query does nothing useful. `ORDER BY` inside an EXISTS subquery cannot change whether at least one row exists, and neither can restricting it to one row: zero rows stay zero, one-or-more stays one-or-more. ## Where EXISTS may appear EXISTS is a predicate, so it belongs anywhere a condition belongs: `WHERE`, `HAVING`, a `CHECK` constraint, the `ON` condition of a join, or the `WHEN` branch of a `CASE` expression. ```sql SELECT c.id, CASE WHEN EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id) THEN 'active' ELSE 'dormant' END AS status FROM customers c; ``` What it is not is an expression that yields a value you can add or concatenate. If you need a count, use `COUNT(*)`; if you need a flag, wrap EXISTS in `CASE` as above. ## Duplicates cannot leak out Because EXISTS collapses any number of matching inner rows to a single yes, it filters the outer row set without ever multiplying it. Ten matching orders and one matching order give the same answer: the customer appears exactly once. This is a property of the predicate itself, and it is a large part of why EXISTS reads so cleanly as "keep rows that have at least one …". ## Common misconceptions - "EXISTS returns the rows of the subquery." It does not; it returns a truth value about them. - "`EXISTS (SELECT NULL)` is UNKNOWN or FALSE." It is TRUE — a row was returned. - "`NOT EXISTS` can be UNKNOWN when there are NULLs." It cannot; it is the exact complement of EXISTS. - "You must project the correlating column inside EXISTS." The select list is unused; the correlation lives in the inner `WHERE`. ## What to say in an interview Define it as at-least-one-row, state that it is two-valued and therefore NULL-proof at the predicate level, note that the select list is not evaluated, and give the `NOT EXISTS` contrast as the practical payoff. That is a complete answer in four sentences.