# Deep Dive — EXISTS

> "Is there at least one?" is a different question from "how many?" — and the database answers the first one by stopping at the first row.

Used in: [Chapter 1 — SQL First](../course-plan.md) · Appendix entry: [EXISTS](../appendix.md#exists) · Related: [UPDATE WHERE EXISTS](update-where-exists.md), [FETCH FIRST](fetch-first.md), [EXCEPTION](exception.md)

---

## 1. What It Is and When

`EXISTS (subquery)` is TRUE as soon as the subquery produces one row. Oracle does not run the subquery to completion. In joins the optimizer turns `EXISTS` into a **semi-join** and `NOT EXISTS` into an **anti-join** — both stop probing per outer row after the first hit.

Use it for: "customers that have orders", "orders without a customer", "skip if already processed" — every membership test. Never use `COUNT(*) > 0` for a membership test on a large table.

## 2. Syntax

```sql
-- in SQL
WHERE [NOT] EXISTS (SELECT 1 FROM <t> WHERE <correlation> [AND ...])

-- in PL/SQL: three idioms for "does a row exist"
SELECT CASE WHEN EXISTS (SELECT 1 FROM <t> WHERE ...) THEN 1 ELSE 0 END INTO v_flag FROM dual;

SELECT COUNT(*) INTO v_cnt FROM <t> WHERE ... AND ROWNUM = 1;     -- 0 or 1, never scans further

BEGIN
  SELECT 1 INTO v_dummy FROM <t> WHERE ... AND ROWNUM = 1;
  -- exists
EXCEPTION
  WHEN NO_DATA_FOUND THEN -- does not exist
END;
```

What goes in the subquery's select list is irrelevant (`SELECT 1`, `SELECT *`, `SELECT NULL` — identical plans).

## 3. Examples on the Course Schema

### 3a. Semi-join — customers with an order this year

```sql
SELECT c.customer_id, c.tier
FROM   customers c
WHERE  EXISTS (SELECT 1 FROM orders o
               WHERE  o.customer_id = c.customer_id
               AND    o.order_date >= DATE '2026-01-01');
```

Plan: `HASH JOIN SEMI` (or `NESTED LOOPS SEMI` for a small driving set). Each customer is matched at most once, even if they have 10,000 orders. The `JOIN ... GROUP BY` or `SELECT DISTINCT ... JOIN` version has to produce and then collapse all 10,000.

### 3b. Anti-join — customers with no orders at all, and the `NOT IN` trap

```sql
-- correct
SELECT c.customer_id
FROM   customers c
WHERE  NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id);

-- returns NO ROWS if orders.customer_id contains a single NULL
SELECT c.customer_id
FROM   customers c
WHERE  c.customer_id NOT IN (SELECT o.customer_id FROM orders o);
```

`x NOT IN (a, b, NULL)` is `x <> a AND x <> b AND x <> NULL` — the last term is UNKNOWN, so the whole predicate is never TRUE. `NOT EXISTS` has no such problem. If you must use `NOT IN`, add `WHERE o.customer_id IS NOT NULL` inside — then the optimizer can use an anti-join too.

### 3c. Membership test in PL/SQL — the three idioms

```sql
DECLARE
  v_has_orders NUMBER;
BEGIN
  -- idiom 1: EXISTS in a CASE — one statement, no exception handling
  SELECT CASE WHEN EXISTS (SELECT 1 FROM orders WHERE customer_id = :id) THEN 1 ELSE 0 END
  INTO   v_has_orders FROM dual;

  -- idiom 2: ROWNUM = 1 — same cost, reads as a count
  SELECT COUNT(*) INTO v_has_orders
  FROM   orders WHERE customer_id = :id AND ROWNUM = 1;

  IF v_has_orders = 1 THEN ... END IF;
END;
```

Both stop at the first row. The one to avoid:

```sql
SELECT COUNT(*) INTO v_cnt FROM orders WHERE customer_id = :id;   -- counts all 10,000
IF v_cnt > 0 THEN ...
```

### 3d. "Skip if already done" — restartable jobs

```sql
INSERT INTO order_summary (customer_id, order_month, total_amount)
SELECT o.customer_id, TRUNC(o.order_date, 'MM'), SUM(o.amount)
FROM   orders o
WHERE  o.order_date >= DATE '2026-01-01'
AND    NOT EXISTS (SELECT 1 FROM order_summary s
                   WHERE  s.customer_id = o.customer_id
                   AND    s.order_month = TRUNC(o.order_date, 'MM'))
GROUP  BY o.customer_id, TRUNC(o.order_date, 'MM');
```

Rerun after a crash and it inserts only what is missing. See [COMMIT](commit.md) on restartable design.

## 4. What Breaks at Scale

- `COUNT(*)` on `orders` for one customer = index range scan over all of that customer's rows (thousands). `EXISTS` = one index probe. Times 5M customers, the difference is hours.
- `IN (subquery)` vs `EXISTS`: the optimizer usually produces the same plan for both (it rewrites either way). The choice is about NULL semantics and readability, not speed — except `NOT IN` on a nullable column, which cannot become an anti-join.
- `EXISTS` inside a PL/SQL loop is still a query per iteration. If the loop itself is the problem, `EXISTS` does not save it — an outer join or an in-memory lookup ([ASSOCIATIVE ARRAY](associative-array.md)) does.

## 5. Pitfalls

1. `SELECT COUNT(*) ... IF cnt > 0` — the reflex to unlearn.
2. `NOT IN` with a nullable column — silently returns nothing.
3. `SELECT ... INTO` without `ROWNUM = 1` for an existence test — `TOO_MANY_ROWS` the day a second row appears.
4. `EXISTS` used where you actually need the matched values — that is a join.
5. In 23ai, `IF EXISTS(...)` style boolean expressions in PL/SQL become simpler; on 19c stick to the idioms above.

## 6. Related

- [UPDATE WHERE EXISTS](update-where-exists.md) — the same predicate driving an update.
- [FETCH FIRST](fetch-first.md) / `ROWNUM = 1` — stopping early in a plain query.
- [EXCEPTION](exception.md) — why `NO_DATA_FOUND` as control flow is expensive per row.