The EXISTS operator tests whether a subquery returns any rows at all. If the subquery returns at least one row, EXISTS evaluates to TRUE and the outer row is included in the result. If the subquery returns no rows, it evaluates to FALSE.
EXISTS is almost always used with a correlated subquery — a subquery that references a column from the outer query, causing it to re-evaluate for each row the outer query examines.
SELECT columns
FROM outer_table AS o
WHERE EXISTS (
SELECT 1
FROM inner_table AS i
WHERE i.foreign_key = o.id
);SELECT inside an EXISTS subquery is typically written as SELECT 1 or SELECT *. The actual values selected do not matter — only whether any row is returned.
Find all customers who have placed at least one order:
SELECT c.name, c.city
FROM customers AS c
WHERE EXISTS (
SELECT 1
FROM orders AS o
WHERE o.customer_id = c.id
);| name | city |
|---|---|
| Rahim Uddin | Dhaka |
| Karim Ali | Sylhet |
For each customer row, MySQL checks whether any row in orders has a matching customer_id. Sara Begum and Nila Akter are excluded because no orders reference their IDs.
NOT EXISTS is the inverse — it includes a row only when the subquery returns no matching rows:
SELECT c.name, c.city
FROM customers AS c
WHERE NOT EXISTS (
SELECT 1
FROM orders AS o
WHERE o.customer_id = c.id
);| name | city |
|---|---|
| Sara Begum | Chittagong |
| Nila Akter | Dhaka |
Both EXISTS and IN can find rows based on a relationship with another table, but they behave differently:
| EXISTS | IN | |
|---|---|---|
| What it checks | Whether any row exists | Whether a value is in a list |
| Stops early? | Yes — at first match | No — evaluates full list |
| NULL handling | Safe — NULL does not cause issues | NOT IN breaks when list contains NULL |
| Best for | Existence checks, large inner tables | Small static lists or small subquery results |
-- These two queries return the same result
-- Using IN:
SELECT name FROM customers
WHERE id IN (SELECT customer_id FROM orders);
-- Using EXISTS (generally preferred for large tables):
SELECT name FROM customers AS c
WHERE EXISTS (SELECT 1 FROM orders WHERE customer_id = c.id);EXISTS returns TRUE when the subquery produces at least one row, FALSE when it produces none.EXISTS subquery do not matter — use SELECT 1 by convention.EXISTS is always a correlated subquery — it references the outer table.NOT EXISTS finds rows in the outer table that have no match in the inner table.EXISTS stops scanning the inner table as soon as one match is found, making it efficient for large inner tables.