# EXISTS and Derived Tables

> Test for related rows with EXISTS and name intermediate results with derived tables.

---

LLMS index: [llms.txt](/en/llms.txt)

---

`EXISTS (subquery)` is true when the subquery returns at least one row. It is the workhorse of "does any related row satisfy X?" queries — usually written as a *correlated* subquery referencing the outer row. A *derived table* is a subquery in the `FROM` clause, given an alias and used like a table. The two together cover most of the SQL you write before you reach CTEs and window functions.

## What you'll learn {#learn}

- `EXISTS` and `NOT EXISTS`: tests for "any row exists" / "no row exists"
- Why `NOT EXISTS` beats `NOT IN` for nullable subqueries
- Derived tables: a subquery in `FROM` with an alias
- When a derived table is clearer than a CTE
- The relationship to `IN` and joins

## Example {#example}

```sql {num="37.1" caption="EXISTS and Derived Tables"}
-- EXISTS: customers who have at least one order
SELECT c.id, c.name
FROM customers_example c
WHERE EXISTS (
  SELECT 1 FROM orders_example o WHERE o.customer_id = c.id
)
ORDER BY c.id;

-- NOT EXISTS: customers with no orders (safe even with NULL customer_id values)
SELECT c.id, c.name
FROM customers_example c
WHERE NOT EXISTS (
  SELECT 1 FROM orders_example o WHERE o.customer_id = c.id
)
ORDER BY c.id;

-- Derived table: a subquery used in FROM, given an alias
SELECT sub.customer_id, sub.total_spent
FROM (
  SELECT customer_id, sum(total) AS total_spent
  FROM orders_example
  GROUP BY customer_id
) AS sub
WHERE sub.total_spent > 30
ORDER BY sub.customer_id;

-- Derived table joined back to a base table
SELECT c.name, latest.total
FROM customers_example c
JOIN (
  SELECT customer_id, max(total) AS total
  FROM orders_example
  GROUP BY customer_id
) AS latest ON latest.customer_id = c.id
ORDER BY c.id;
```

The convention `SELECT 1` inside `EXISTS` is not magic — PostgreSQL does not care what columns the subquery returns; it only checks whether any row would be produced. `SELECT 1`, `SELECT *`, `SELECT NULL` all work the same. `SELECT 1` reads as "I do not care about the columns" and is the conventional choice.

A *derived table* — also called an *inline view* — is just a subquery in `FROM`. An explicit alias (after the closing parenthesis) gives the result a name you can refer to. Derived tables compose well; you can join them, filter them, group them, even nest them. The main downside is that a complex derived table buries the logic in the middle of the query. CTEs (next lesson) are usually a nicer way to name an intermediate result.

## Run the example {#run}

To run (requires `customers_example` and `orders_example`):

```text
$ psql -f source/exists-and-derived-tables.sql postgres
 id | name
----+------
  1 | Alice
(1 row)

 id | name
----+------
  2 | Bob
  3 | Carol
(2 rows)
 ...
```

## Common pitfalls {#pitfalls}

- `IN (SELECT col FROM t)` and `EXISTS (SELECT 1 FROM t WHERE t.col = outer.col)` are usually equivalent — but `NOT IN` and `NOT EXISTS` are not when NULLs are possible. Prefer `NOT EXISTS` whenever NULLs are on the table.
- An alias such as `AS x` is useful for clarity and portability. PostgreSQL 16 and later allow subqueries in `FROM` without one; older PostgreSQL versions require an alias.
- A derived table with `ORDER BY` inside is allowed but the order is not preserved when the outer query consumes it — sort outside.
- Some old guides said `EXISTS` is "always slower than `IN`." On modern PostgreSQL the planner handles both well; choose for correctness and readability first.

> [!TIP] Tip
> `EXISTS` short-circuits — once it finds the first matching row, it stops. So `WHERE EXISTS (SELECT 1 FROM big_table WHERE ...)` can be very fast if the matching row is found early.

> [!NOTE] Try it
> Find fruits that have a name appearing exactly once: `SELECT name FROM fruits f WHERE NOT EXISTS (SELECT 1 FROM fruits f2 WHERE f2.name = f.name AND f2.id <> f.id);`. Then rewrite a derived table as a CTE in your head (preview of the next lesson): `WITH spend AS (SELECT customer_id, sum(total) AS total_spent FROM orders_example GROUP BY customer_id) SELECT * FROM spend WHERE total_spent > 30;`.

## Downloads {#downloads}

SQL file: [exists-and-derived-tables.sql](/examples/source/exists-and-derived-tables.sql) · [All lesson files](/examples/examples.zip) · [Setup guide](/en/#prerequisites)