EXISTS

EXISTS tests whether a subquery returns at least one row, commonly used to check for the presence of related records without caring about their actual values.

Overview

EXISTS (subquery) returns TRUE if the subquery produces at least one row, and FALSE if it produces none. Unlike most predicates, EXISTS doesn't care about the values in the subquery's result -- only whether any rows come back -- so subqueries used with EXISTS conventionally select a constant like SELECT 1 rather than real columns.

Copy code
SELECT c.customer_id, c.name
FROM customers c
WHERE EXISTS (
  SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id
);

This returns every customer who has placed at least one order, correlating the inner query to the outer one via o.customer_id = c.customer_id.

NOT EXISTS

NOT EXISTS is the standard, NULL-safe way to find rows with no matching related record -- and is generally preferred over NOT IN for this purpose, since NOT IN can unexpectedly return zero rows if the subquery contains a NULL:

Copy code
SELECT c.customer_id, c.name
FROM customers c
WHERE NOT EXISTS (
  SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id
);

EXISTS vs IN vs JOIN

EXISTS checks for row existence and stops as soon as one matching row is found -- it doesn't need to fetch or compare actual values.
IN compares a value against a list or subquery result set.
A JOIN (or semi-join) can often express the same logic, but a plain INNER JOIN will produce duplicate outer rows if there are multiple matches, whereas EXISTS always returns each outer row at most once.

DuckDB's query optimizer typically rewrites correlated EXISTS/NOT EXISTS subqueries into efficient semi-joins and anti-joins internally, so there's no inherent performance penalty to writing them as subqueries for readability.

Performance note

Because EXISTS short-circuits at the first matching row, it can be more efficient than a COUNT(*) > 0 subquery, which forces a full count before comparing.

Related terms

IN operator →

The IN operator tests whether a value matches any value in a given list or subquery, providing a concise alternative to multiple OR conditions.

LATERAL JOIN →

A LATERAL join lets a subquery in the FROM clause reference columns from earlier tables in the same FROM clause, enabling per-row correlated logic without a scalar subquery.

JOIN clause →

How do SQL JOINs work? Learn INNER, LEFT, RIGHT, FULL OUTER, CROSS, and ASOF joins with syntax examples — plus how to join directly against CSV and Parquet files.

WHERE clause →

The WHERE clause filters rows in a SQL query based on a boolean condition, keeping only rows for which the condition evaluates to true.

EXCEPT →

EXCEPT returns the rows produced by the first query that do not appear in the result of a second query, effectively computing a set difference.

GREATEST and LEAST →

GREATEST and LEAST return the largest or smallest value, respectively, among a list of expressions passed as arguments.

FAQS

EXISTS checks whether a correlated subquery returns any rows at all, while IN compares a specific value against a list or subquery's result set; EXISTS is generally safer when NULLs might be present in the related data.

NOT IN can silently return zero rows if the subquery result contains a NULL; NOT EXISTS with a correlated subquery doesn't have this problem and is the standard safe pattern for "find rows with no match."

No -- EXISTS only checks for row presence, so the convention SELECT 1 is used to signal that the actual selected values are irrelevant.