---
title: "EXISTS"
description: "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."
canonical: "https://motherduck.com/glossary/exists/"
related:
  - title: "DuckDB SQL | MotherDuck Docs"
    url: "https://motherduck.com/docs/sql-reference/duckdb-sql-reference/"
  - title: "SQL Golf: 5 SQL Tricks You Should (Probably) Never Use"
    url: "https://motherduck.com/blog/its-a-sql-golf-quackmas/"
  - title: "RESULT | MotherDuck Docs"
    url: "https://motherduck.com/docs/sql-reference/motherduck-sql-reference/result/"
gated_asset:
  title: "DuckLake: The Lakehouse Table Format"
  url: "https://motherduck.com/lp/ducklake-lakehouse-table-format-book-full/"
---

# 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.

```sql
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`:

```sql
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.