MySQL EXISTS and NOT EXISTS: Check for Related Rows

EXISTS (subquery) is true if the subquery returns at least one row, and false if it returns none. It does not care about the values, only about existence.

query.sql

SQL

CREATE TABLE customers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    city VARCHAR(50) NOT NULL
);

CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    customer_id INT NOT NULL,
    ordered_on DATE NOT NULL,
    total DECIMAL(10,2) NOT NULL,
    status VARCHAR(20) NOT NULL
);

INSERT INTO customers (name, city) VALUES
    ('Maya Shah',    'Pune'),
    ('Rohan Verma',  'Delhi'),
    ('Sana Ali',     'Pune'),
    ('Tara Nair',    'Kochi'),
    ('Uday Singh',   'Delhi');

INSERT INTO orders (customer_id, ordered_on, total, status) VALUES
    (1, '2026-05-02',  680.00, 'delivered'),
    (1, '2026-05-20', 1299.00, 'delivered'),
    (2, '2026-05-21',  145.00, 'shipped'),
    (3, '2026-06-01',  450.00, 'delivered'),
    (3, '2026-06-05',   80.00, 'cancelled'),
    (3, '2026-06-18',  999.00, 'shipped'),
    (2, '2026-06-20',  220.00, 'pending');

Customers who have at least one order

query.sql

SQL

SELECT c.id, c.name
FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id)
ORDER BY c.id;

Output

OUTPUT

+----+-------------+
| id | name        |
+----+-------------+
|  1 | Maya Shah   |
|  2 | Rohan Verma |
|  3 | Sana Ali    |
+----+-------------+
3 rows in set

For each customer, MySQL asks: “Is there any order with this customer’s id?” It stops at the first match. That is why EXISTS is fast for existence checks.

SELECT 1 is a convention. The subquery’s columns are never read. SELECT * or SELECT NULL behave the same.

NOT EXISTS: rows without a match

query.sql

SQL

SELECT c.id, c.name
FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id)
ORDER BY c.id;

Output

OUTPUT

+----+------------+
| id | name       |
+----+------------+
|  4 | Tara Nair  |
|  5 | Uday Singh |
+----+------------+
2 rows in set

Customers who never ordered. NOT EXISTS is the safest form of this question, because it is not affected by NULL values (Finding Missing Rows).

Extra conditions inside

Put the conditions that describe the related rows inside the subquery:

query.sql

SQL

SELECT c.name
FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id AND o.status = 'delivered' AND o.total > 1000)
ORDER BY c.name;

Output

OUTPUT

+-----------+
| name      |
+-----------+
| Maya Shah |
+-----------+
1 row in set

Customers with a delivered order over 1000 (only Maya). A customer with a delivered order of 450 and a shipped order of 999 does not qualify: both conditions must hold for the same order.

Several conditions combined

query.sql

SQL

SELECT c.name
FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id AND o.status = 'pending')
  AND NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id AND o.status = 'delivered')
ORDER BY c.name;

Output

OUTPUT

+-------------+
| name        |
+-------------+
| Rohan Verma |
+-------------+
1 row in set

“Has a pending order and no delivered order”: Rohan. Combining EXISTS and NOT EXISTS is a clear way to express questions about different kinds of related rows.

EXISTS vs IN vs JOIN

All three can answer “customers with orders”:

query.sql

SQL

1
2

SELECT c.id FROM customers c WHERE c.id IN (SELECT customer_id FROM orders) ORDER BY c.id;
SELECT DISTINCT c.id FROM customers c JOIN orders o ON o.customer_id = c.id ORDER BY c.id;

Output

OUTPUT

+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
+----+
3 rows in set

+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
+----+
3 rows in set

	`EXISTS`	`IN (subquery)`	`JOIN`
Duplicates	One row per customer	One row per customer	One row per order, so needs `DISTINCT`
NULL-safe	Yes	`NOT IN` is not	Yes
Can return columns of the child	No	No	Yes
Stops at first match	Yes	The optimizer decides	No

If you only need to test for a related row, use EXISTS. If you need columns from the related rows, use a JOIN.

The optimizer turns it into a semi-join

MySQL 8 does not run an EXISTS subquery naively for each row. It converts it to a semi-join, which can use a join order and indexes of its own choosing. In EXPLAIN, you may see strategies called FirstMatch, LooseScan, Materialization or Duplicate Weedout. You do not need to choose. Just keep an index on the correlating column (orders.customer_id).

Existence check in application code

“Does this email already exist?”:

query.sql

SQL

SELECT EXISTS (SELECT 1 FROM customers WHERE name = 'Maya Shah') AS maya_exists, EXISTS (SELECT 1 FROM customers WHERE name = 'Nobody') AS nobody_exists;

Output

OUTPUT

+-------------+---------------+
| maya_exists | nobody_exists |
+-------------+---------------+
|           1 |             0 |
+-------------+---------------+
1 row in set

SELECT EXISTS (...) returns 1 or 0. It is faster than SELECT COUNT(*) ... > 0, because it can stop at the first row. (For uniqueness, still use a UNIQUE index, since a check-then-insert is not race-safe.)

EXISTS with an empty subquery table

query.sql

SQL

1
2
3

SELECT 'orders exist' AS answer WHERE EXISTS (SELECT 1 FROM orders)
UNION ALL
SELECT 'no refunds table rows' WHERE NOT EXISTS (SELECT 1 FROM (SELECT 1 AS x WHERE FALSE) AS no_rows);

Output

OUTPUT

+-----------------------+
| answer                |
+-----------------------+
| orders exist          |
| no refunds table rows |
+-----------------------+
2 rows in set

Common mistakes

Forgetting the correlation. EXISTS (SELECT 1 FROM orders) without a WHERE linking to the outer row is true for every customer if the table has any row.
**Using COUNT(*) > 0** for existence on a big table.
Using NOT IN when NOT EXISTS is the safe choice.

lightbulb

Say it in words

WHERE EXISTS (...) reads as “where there exists …”. If you can say the question that way, EXISTS is the right tool.