EXISTS and NOT EXISTS

EXISTS only asks whether a subquery produced a row, which makes NOT EXISTS the NULL-safe way to write an anti-join.

SyntaxWHERE [NOT] EXISTS (SELECT 1 FROM t2 WHERE t2.fk = t1.id)

EXISTS (subquery) is TRUE if the subquery returns any rows at all and FALSE otherwise; NOT EXISTS inverts that. It is a pure yes-or-no question about the presence of matching rows, so it never multiplies the outer result the way a join can.

The select list is ignored

The MySQL 8.4 manual is explicit that the SELECT list of an EXISTS subquery is ignored. These three are the same statement:

WHERE EXISTS (SELECT * FROM t2)
WHERE EXISTS (SELECT 5 FROM t2)
WHERE EXISTS (SELECT column1 FROM t2)

EXISTS (TABLE t2) is shorthand for EXISTS (SELECT * FROM t2) and behaves identically. Choosing SELECT 1 over SELECT * is a style preference, not an optimisation. If t2 contains any row — even a row that is nothing but NULLs — EXISTS is TRUE.

Why NOT EXISTS beats NOT IN

NOT IN is an alias for <> ALL, so a NULL in the inner column turns the comparison UNKNOWN and the whole result goes empty. NOT EXISTS asks only whether a matching row was produced; an inner NULL simply fails to match and the outer row is correctly kept. There is a performance argument too: since MySQL 8.0.17 the optimizer rewrites NOT IN and NOT EXISTS into an antijoin, but the manual notes this transformation usually cannot be applied when the compared expressions are nullable.

Example

Example · sql
-- Products that have never appeared on an order line.
SELECT p.id, p.name, p.price
FROM products AS p
WHERE NOT EXISTS (
        SELECT 1                     -- ignored: *, 1 or a column all behave the same
          FROM order_items AS oi
         WHERE oi.product_id = p.id  -- the correlation; without it this is a global test
      )
ORDER BY p.name;

When to use it

  • List parent rows that have no child rows, such as products never ordered.
  • Filter to customers who have at least one order in a status, without duplicating the customer.
  • Guard a DELETE so it only removes rows nothing else references.
  • Replace a NOT IN that broke when a nullable column appeared in the subquery.

More examples

EXISTS and IN answering the same question

For the positive case these are equivalent in meaning and, in MySQL 8.4, usually in plan too: the manual states that a statement with an EXISTS predicate is subject to the same semijoin transforms as the equivalent IN predicate. Choose on readability. EXISTS reads better when the condition involves several columns of the inner table; IN reads better when you are matching a single key.

Example · sql
-- Existence test, correlated: stops caring once one matching row is found.
SELECT c.id, c.name
FROM customers AS c
WHERE EXISTS (SELECT 1 FROM orders AS o
               WHERE o.customer_id = c.id AND o.status = 'paid');

-- Set membership, uncorrelated: builds the set of customer_ids, then tests c.id.
SELECT c.id, c.name
FROM customers AS c
WHERE c.id IN (SELECT o.customer_id FROM orders AS o WHERE o.status = 'paid');

-- Shorthand form of an unfiltered existence test: TABLE t is SELECT * FROM t.
SELECT 'orders table is not empty' WHERE EXISTS (TABLE orders);

NOT EXISTS as the safe anti-join

Both forms are anti-joins and both are NULL-safe, unlike NOT IN. The NOT EXISTS version states the intent directly and cannot be broken by someone later moving a condition from ON to WHERE. The LEFT JOIN version is worth knowing because you will meet it constantly in existing code; check that the IS NULL test targets a column that is never NULL in a matched row, such as the primary key.

Example · sql
-- Customers with no order at all in 2026.
-- Correct even though orders.customer_id is nullable.
SELECT c.id, c.name
FROM customers AS c
WHERE NOT EXISTS (
        SELECT 1
          FROM orders AS o
         WHERE o.customer_id = c.id
           AND o.ordered_at >= '2026-01-01'
           AND o.ordered_at <  '2027-01-01'
      );

-- The LEFT JOIN spelling of the same anti-join.
SELECT c.id, c.name
FROM customers AS c
LEFT JOIN orders AS o
       ON o.customer_id = c.id
      AND o.ordered_at >= '2026-01-01'
      AND o.ordered_at <  '2027-01-01'
WHERE o.id IS NULL;   -- must test a NOT NULL column of the outer-joined table

Nested NOT EXISTS for 'has all of'

This is relational division, and double NOT EXISTS is the standard way to write it. Reach for it when the requirement is 'has all of' rather than 'has any of': every required skill, every mandatory document signed, full coverage of a bundle. The alternative is a COUNT(DISTINCT ...) in a HAVING clause compared to the size of the required set, which is often faster but harder to keep correct when the required set changes.

Example · sql
-- Customers who have bought EVERY product in category 7.
-- Read it as: no product in the category for which this customer has no order line.
SELECT c.id, c.name
FROM customers AS c
WHERE NOT EXISTS (
        SELECT 1
          FROM products AS p
         WHERE p.category_id = 7
           AND NOT EXISTS (
                 SELECT 1
                   FROM order_items AS oi
                   JOIN orders AS o ON o.id = oi.order_id
                  WHERE oi.product_id = p.id
                    AND o.customer_id  = c.id
               )
      );

Related

sql in subquerysql anti joinsql correlatedsql nullsql left join

Discussion

  • Be the first to comment on this lesson.