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
Appendix entry
EXISTS
Related
UPDATE WHERE EXISTS FETCH FIRST EXCEPTION

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.

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

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.

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.

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

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 on restartable design.

  • 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) does.

  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.