EXISTS

What EXISTS Actually Tests

EXISTS (subquery) returns a plain boolean: TRUE if the subquery produces at least one row, FALSE if it produces zero rows. Crucially, EXISTS does not care what the row's column values are — it could SELECT 1, SELECT *, or SELECT anything; only row presence matters. This is why SELECT 1 inside an EXISTS subquery is conventional — the optimizer never needs the actual values.

Sample data used throughout this chapter:

employees

departments

A Correlated EXISTS Example

sql
SELECT d.name
FROM departments d
WHERE EXISTS (
  SELECT 1 FROM employees e WHERE e.department = d.name
);

Walk through each department:

• Engineering → Alice/Bob/Carol match → EXISTS = TRUE
• Sales → Dave/Eve/Frank match → TRUE
• Marketing → Grace/Heidi match → TRUE
• HR → no employees at all → EXISTS = FALSE

Result:

HR is correctly excluded since no employee has department = 'HR'.

Why EXISTS Can Beat IN with a Subquery

IN (SELECT department FROM employees) has to (conceptually) build the entire set of matching values before testing membership. EXISTS, on the other hand, can short-circuit: as soon as the engine finds one matching employee row for a department, it stops scanning and reports TRUE — it never needs to find all of them. For a department with thousands of matching employees, EXISTS only needs to locate the first one; a naive IN set-construction doesn't have that luxury in the same way, though modern optimizers often narrow this gap by rewriting IN into a semi-join too.

EXISTS Is (Almost Always) Correlated

While EXISTS (SELECT 1 FROM some_table) with no outer reference is syntactically legal, it's rarely useful — it just answers "is this table non-empty?" once for the whole query. The real power of EXISTS comes from correlation: testing, per outer row, whether a related row exists somewhere else.

Edge Cases

• EXISTS never returns NULL — it's always definitively TRUE or FALSE, unlike scalar/IN comparisons that can produce UNKNOWN when NULLs are involved.
• Selecting more columns inside an EXISTS subquery (SELECT * FROM ...) costs nothing extra in practice — engines recognize EXISTS and skip fetching column data; SELECT 1 is a style convention, not a strict performance requirement in most modern databases.
• An EXISTS subquery can contain its own WHERE, GROUP BY, HAVING — any valid query shape; only the row-count-nonzero result matters in the end.

Key Takeaways / Q&A

Q: Does EXISTS ever error out due to "too many rows," like `=` would? A: No — EXISTS is happy with any number of matching rows, from 1 to millions; it only asks whether that number is zero or not.

Q: Is `SELECT 1` required inside EXISTS? A: No, it's a convention signaling "we only care about row existence"; SELECT * or any column list works identically.

Q: When is EXISTS clearer than IN? A: When the real question is "does at least one related row exist?" rather than "does this specific value appear in that list?"