EXISTS Operator

The PostgreSQL EXISTS operator is a boolean operator that checks whether a subquery returns any rows. It returns true if the subquery produces at least one row, and false if it produces none. EXISTS is evaluated purely on row presence — column values are irrelevant.

EXISTS syntax

SELECT select_list
FROM table1
WHERE EXISTS (
  SELECT 1
  FROM table2
  WHERE condition
);

The convention is to write SELECT 1 inside EXISTS because the column values are never used. The subquery is typically a correlated subquery that references the outer query's current row.

Basic EXISTS check

This query checks whether any payment with an amount of zero exists in the payment table:

SELECT EXISTS (
  SELECT 1
  FROM payment
  WHERE amount = 0
);

Output:

exists
--------
t
(1 row)

The result is t (true) because at least one row matches the condition.

Using EXISTS to filter rows

This query finds customers who have made at least one payment greater than 11:

SELECT first_name, last_name
FROM customer c
WHERE EXISTS (
  SELECT 1
  FROM payment p
  WHERE p.customer_id = c.customer_id
    AND amount > 11
)
ORDER BY first_name, last_name;

Output:

first_name | last_name
------------+-----------
Karen      | Jackson
Kent       | Arsenault
Nicholas   | Barfield
Rosemary   | Schmidt
Tanya      | Gilbert
Terrance   | Roush
Vanessa    | Sims
Victoria   | Gibson
(8 rows)

For each customer in the outer query, the correlated subquery checks whether a matching payment exists. EXISTS stops checking as soon as it finds the first qualifying payment row.

Using NOT EXISTS to find absent relationships

NOT EXISTS finds customers who have never made a payment greater than 11:

SELECT first_name, last_name
FROM customer c
WHERE NOT EXISTS (
  SELECT 1
  FROM payment p
  WHERE p.customer_id = c.customer_id
    AND amount > 11
)
ORDER BY first_name, last_name;

This returns the complement — every customer not found in the EXISTS query above. NOT EXISTS is the standard way to implement "anti-join" logic in SQL.

EXISTS and NULL behavior

If the subquery returns NULL, EXISTS still returns true because a row was produced:

-- Returns every customer because the subquery always produces a row (NULL)
SELECT first_name, last_name
FROM customer
WHERE EXISTS (SELECT NULL)
ORDER BY first_name, last_name;

This confirms that EXISTS evaluates row presence only — not the truthiness of any returned value.

Tips for using EXISTS effectively

• Use SELECT 1 inside EXISTS as a clear signal that only row existence matters, not column data.
• EXISTS short-circuits after finding the first matching row, which can make it faster than IN when the subquery matches many rows.
• Use NOT EXISTS instead of NOT IN when the subquery column may contain NULLs — NOT IN with any NULL in the set returns NULL (unknown) for every row, silently eliminating all results.
• Index the correlated join columns (e.g., payment.customer_id) to make the inner lookup fast for each outer row.

Reference: PostgreSQL documentation — Subquery Expressions (EXISTS).