EXISTS and NOT EXISTS

40 min · Intermediate

Many business questions are yes-or-no questions about related rows: has this customer ever ordered, does this department have anyone, has this student failed any subject. EXISTS answers exactly that, and NOT EXISTS finds the rows where the answer is no.

EXISTS checks for at least one row

EXISTS (subquery) is true when the subquery returns at least one row and false when it returns none. It never looks at the values, only at whether a row came back.

Has this customer ever placed an order? The answer is only yes or no, so there is no need to list or count the orders.

Example 1 · Customers who have ordered, and EXISTS as a value

SQL

1-- Customers with at least one order2SELECT c.name3FROM customers c4WHERE EXISTS (5    SELECT 16    FROM orders o7    WHERE o.customer_id = c.customer_id8)9ORDER BY c.name;10
11-- EXISTS on its own returns 1 (true) or 0 (false)12SELECT EXISTS (SELECT 1 FROM orders WHERE status = 'PLACED')           AS any_placed,13       EXISTS (SELECT 1 FROM orders WHERE order_date >= '2026-01-01') AS any_in_2026;

Output

+--------------+
| name         |
+--------------+
| Arjun Mehta  |
| Asha Rao     |
| Divya Nair   |
| Kabir Singh  |
| Meena Iyer   |
| Priya Sharma |
| Rahul Verma  |
| Ravi Kumar   |
| Sneha Patil  |
+--------------+
+------------+-------------+
| any_placed | any_in_2026 |
+------------+-------------+
|          1 |           0 |
+------------+-------------+

What this code does

For each customer c, the subquery looks for an order with that customer's id. Nine customers have one; Imran Khan has none, so he is missing.
SELECT 1 is a habit, not a rule: EXISTS only checks whether a row came back, never which columns it has.
The second query shows EXISTS as a plain yes/no value: there are orders still PLACED (1), and none from 2026 (0).

Example 2 · Customers with a delivered order

SQL

1-- Customers with at least one DELIVERED order2SELECT c.name, c.city3FROM customers c4WHERE EXISTS (5    SELECT 16    FROM orders o7    WHERE o.customer_id = c.customer_id8      AND o.status = 'DELIVERED'9)10ORDER BY c.name;

Output

+--------------+-----------+
| name         | city      |
+--------------+-----------+
| Arjun Mehta  | Mumbai    |
| Asha Rao     | Bengaluru |
| Divya Nair   | Kochi     |
| Kabir Singh  | Delhi     |
| Priya Sharma | Pune      |
| Ravi Kumar   | Chennai   |
+--------------+-----------+

What this code does

The extra condition AND o.status = 'DELIVERED' sits inside the subquery, so it asks "does this customer have a delivered order?", not just "any order?".
Asha Rao has two delivered orders (1 and 3) but appears once: EXISTS is true or false per customer, it does not count matches.
Customers whose orders are only cancelled, still placed or missing, such as Meena Iyer and Imran Khan, do not appear.

MySQL can also stop searching at the first matching row, because one row is enough to say yes. EXISTS is almost always correlated: the subquery mentions the outer row, as in WHERE o.customer_id = c.customer_id. Without that link the subquery asks about the whole table, so it gives the same answer for every outer row. EXISTS can also be used on its own in SELECT, where it returns 1 or 0.

Predict the output

Predict EXISTS

EXISTS returns 1 for true and 0 for false.

Round 1 of 2

Orders 7 and 10 are SHIPPED.

SELECT EXISTS (

SELECT 1 FROM orders WHERE status = 'SHIPPED'

) AS any_shipped;

Forgetting the link to the outer row is the most common EXISTS bug. The query runs without error, but returns either every row or none.

NOT EXISTS for missing data

NOT EXISTS (subquery) is true when the subquery returns no rows. It is the clearest way to find missing data, such as customers who never ordered.

Which customers have no order at all? Keep a customer only when the search for their orders comes back empty.

Example 3 · NOT EXISTS: customers and departments with nothing

SQL

1-- Customers who have never placed an order2SELECT c.name, c.city3FROM customers c4WHERE NOT EXISTS (5    SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id6)7ORDER BY c.name;8
9-- Departments with no employees10SELECT d.dept_name, d.location11FROM departments d12WHERE NOT EXISTS (13    SELECT 1 FROM employees e WHERE e.dept_id = d.dept_id14)15ORDER BY d.dept_name;

Output

+------------+-----------+
| name       | city      |
+------------+-----------+
| Imran Khan | Hyderabad |
+------------+-----------+
+-----------+-----------+
| dept_name | location  |
+-----------+-----------+
| Research  | Hyderabad |
+-----------+-----------+

What this code does

For Imran Khan the subquery finds no order, so NOT EXISTS is true and he is kept. For every other customer it finds at least one, so they are dropped.
The second query is the same pattern on other tables: the Research department in Hyderabad has nobody yet.

Example 4 · No mark below 60, and the student with no marks

SQL

1SELECT s.name2FROM students s3WHERE NOT EXISTS (4    SELECT 15    FROM enrollments e6    WHERE e.student_id = s.student_id7      AND e.marks < 608)9ORDER BY s.name;10
11-- Only students who took at least one course12SELECT s.name13FROM students s14WHERE EXISTS     (SELECT 1 FROM enrollments e WHERE e.student_id = s.student_id)15  AND NOT EXISTS (SELECT 1 FROM enrollments e WHERE e.student_id = s.student_id16                                              AND e.marks < 60)17ORDER BY s.name;

Output

+-----------------+
| name            |
+-----------------+
| Aditya Joshi    |
| Bhavana Reddy   |
| Deepika Nair    |
| Fathima Begum   |
| Harini Krishnan |
+-----------------+
+---------------+
| name          |
+---------------+
| Aditya Joshi  |
| Bhavana Reddy |
| Deepika Nair  |
| Fathima Begum |
+---------------+

What this code does

Chirag (58), Eshan (45) and Gaurav (38) each have a mark below 60, so they are removed. Eshan's NULL mark does not count: NULL < 60 is unknown, not true.
Harini Krishnan is in the first result although she has no marks at all. With zero enrollments there is no mark below 60, so NOT EXISTS is true.
The second query adds an EXISTS check for "took at least one course", which removes Harini.

Keeping the rows that have no match is called an anti-join. Add conditions inside the subquery to ask a sharper question, such as "students with no mark below 60", and read the result carefully: a student with no enrollments passes that test too, so add an EXISTS check if you want only students who took at least one exam.

Fix the error

Fix the empty result

The query should list customers who never ordered. Pick the line that fixes it.

Imran Khan (customer 10) should be listed.

SELECT c.name

FROM customers c

WHERE NOT EXISTS (SELECT 1 FROM orders o);

Error

No error, but the result is empty: the subquery is never empty.

NOT IN and the NULL trap

The NOT IN trap: if the list a NOT IN subquery returns contains even one NULL, the condition is never true and the query returns no rows, with no error.

The NOT IN trap: if the list from a NOT IN subquery contains even one NULL, the condition is never true and the query returns no rows. There is no error and no warning.

SQL

SELECT 1 NOT IN (2, 3, NULL) AS not_in_with_null,       1 NOT IN (2, 3)       AS not_in_clean,       1 IN (1, NULL)        AS in_found,       1 IN (2, NULL)        AS in_not_found;

Output

+------------------+--------------+----------+--------------+
| not_in_with_null | not_in_clean | in_found | in_not_found |
+------------------+--------------+----------+--------------+
|             NULL |            1 |        1 |         NULL |
+------------------+--------------+----------+--------------+

Example 5 · The NOT IN trap: employees who are not managers

SQL

1-- The manager_id list contains a NULL (Vikram Rao has no manager)2SELECT COUNT(*) AS not_managers3FROM employees4WHERE emp_id NOT IN (SELECT manager_id FROM employees);5
6-- Fix 1: NOT EXISTS7SELECT e.name8FROM employees e9WHERE NOT EXISTS (SELECT 1 FROM employees r WHERE r.manager_id = e.emp_id)10ORDER BY e.name;11
12-- Fix 2: remove the NULLs from the list13SELECT COUNT(*) AS not_managers14FROM employees15WHERE emp_id NOT IN (SELECT manager_id FROM employees WHERE manager_id IS NOT NULL);

Output

+--------------+
| not_managers |
+--------------+
|            0 |
+--------------+
+----------------+
| name           |
+----------------+
| Amit Shah      |
| Farah Ali      |
| Karthik Raj    |
| Manoj Pillai   |
| Pooja Gupta    |
| Rohan Das      |
| Suresh Babu    |
| Tanvi Kulkarni |
+----------------+
+--------------+
| not_managers |
+--------------+
|            8 |
+--------------+

What this code does

The first query returns 0, although 8 of the 13 employees manage nobody. One NULL in the list makes every NOT IN test unknown.
NOT EXISTS asks "is there an employee whose manager is this person?" and never compares with the NULL, so it lists the 8 correctly.
Fix 2 keeps NOT IN but filters the NULL out first, and the count becomes 8.

The reason is how SQL handles unknown values. x NOT IN (2, 3, NULL) means x <> 2 AND x <> 3 AND x <> NULL. Any comparison with NULL is unknown, and AND with an unknown part can never be true. WHERE keeps only rows where the condition is true, so it keeps nothing. IN has no such problem when it finds a match, because x = 1 is true and one true part is enough for OR.

In the practice database, Vikram Rao has no manager, so manager_id contains a NULL. "Employees who are not managers" with NOT IN (SELECT manager_id FROM employees) therefore returns nobody. Two fixes: use NOT EXISTS, which only asks whether a row exists, or filter the NULLs out of the list with WHERE manager_id IS NOT NULL.

Predict the output

Predict the NOT IN trap

Five employees are managers (ids 1, 2, 6, 9, 11). Vikram Rao, the top person, has manager_id NULL.

Round 1 of 2

SELECT COUNT(*)

FROM employees

WHERE emp_id NOT IN (SELECT manager_id FROM employees);

Prefer NOT EXISTS over NOT IN with a subquery. Use NOT IN only with a fixed list you typed, or a column declared NOT NULL.

EXISTS versus IN versus JOIN

A semi-join returns each row of one table that has at least one match in another table, without repeating the row. EXISTS and IN both work as semi-joins.

The same question can often be asked three ways. EXISTS and IN both filter the outer table and return each outer row at most once.

Example 6 · Three ways: who bought an accessory?

SQL

1-- 1. EXISTS2SELECT c.name3FROM customers c4WHERE EXISTS (5    SELECT 16    FROM orders o7    JOIN order_items oi ON oi.order_id = o.order_id8    JOIN products p     ON p.product_id = oi.product_id9    WHERE o.customer_id = c.customer_id10      AND p.category_id = 411)12ORDER BY c.name;13
14-- 2. IN15SELECT name16FROM customers17WHERE customer_id IN (18    SELECT o.customer_id19    FROM orders o20    JOIN order_items oi ON oi.order_id = o.order_id21    JOIN products p     ON p.product_id = oi.product_id22    WHERE p.category_id = 423)24ORDER BY name;25
26-- 3. JOIN: one row per matching item27SELECT c.name28FROM customers c29JOIN orders o       ON o.customer_id = c.customer_id30JOIN order_items oi ON oi.order_id = o.order_id31JOIN products p     ON p.product_id = oi.product_id32WHERE p.category_id = 433ORDER BY c.name;

Output

+-------------+
| name        |
+-------------+
| Asha Rao    |
| Kabir Singh |
| Rahul Verma |
| Ravi Kumar  |
+-------------+
+-------------+
| name        |
+-------------+
| Asha Rao    |
| Kabir Singh |
| Rahul Verma |
| Ravi Kumar  |
+-------------+
+-------------+
| name        |
+-------------+
| Asha Rao    |
| Asha Rao    |
| Asha Rao    |
| Kabir Singh |
| Rahul Verma |
| Ravi Kumar  |
| Ravi Kumar  |
+-------------+

What this code does

EXISTS and IN give the same four customers, each once. A subquery may itself contain joins, as both do here.
The plain join returns one row per accessory line: Asha Rao bought accessories in three orders, so she appears three times, and Ravi Kumar twice. SELECT DISTINCT c.name would fix it.
Use the join version only when you need columns from the other tables, such as the product name.

A join returns one row per match, so a customer with three matching orders appears three times unless you add DISTINCT. For "has none" questions, the choices are NOT EXISTS, NOT IN and the LEFT JOIN ... WHERE right.id IS NULL pattern from the Joins module. NOT EXISTS and the LEFT JOIN version handle NULLs correctly; NOT IN does not. On speed, MySQL 8 turns most IN and EXISTS subqueries into efficient semi-joins and anti-joins internally (a semi-join keeps rows that have a match), so the old advice that "EXISTS is always faster than IN" is no longer a rule. Choose by meaning and safety first.

Yes/no question about related rows → EXISTS or IN.
"Has none" question → NOT EXISTS (or LEFT JOIN ... IS NULL).
NOT IN with a subquery → only if the column cannot be NULL.

Next step

Check your understanding

12 quick questions

1-- Customers with at least one order2SELECT c.name3FROM customers c4WHERE EXISTS (5 SELECT 16 FROM orders o7 WHERE o.customer_id = c.customer_id8)9ORDER BY c.name;10 11-- EXISTS on its own returns 1 (true) or 0 (false)12SELECT EXISTS (SELECT 1 FROM orders WHERE status = 'PLACED') AS any_placed,13 EXISTS (SELECT 1 FROM orders WHERE order_date >= '2026-01-01') AS any_in_2026;

1-- Customers with at least one DELIVERED order2SELECT c.name, c.city3FROM customers c4WHERE EXISTS (5 SELECT 16 FROM orders o7 WHERE o.customer_id = c.customer_id8 AND o.status = 'DELIVERED'9)10ORDER BY c.name;

1-- Customers who have never placed an order2SELECT c.name, c.city3FROM customers c4WHERE NOT EXISTS (5 SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id6)7ORDER BY c.name;8 9-- Departments with no employees10SELECT d.dept_name, d.location11FROM departments d12WHERE NOT EXISTS (13 SELECT 1 FROM employees e WHERE e.dept_id = d.dept_id14)15ORDER BY d.dept_name;

1SELECT s.name2FROM students s3WHERE NOT EXISTS (4 SELECT 15 FROM enrollments e6 WHERE e.student_id = s.student_id7 AND e.marks < 608)9ORDER BY s.name;10 11-- Only students who took at least one course12SELECT s.name13FROM students s14WHERE EXISTS (SELECT 1 FROM enrollments e WHERE e.student_id = s.student_id)15 AND NOT EXISTS (SELECT 1 FROM enrollments e WHERE e.student_id = s.student_id16 AND e.marks < 60)17ORDER BY s.name;

NOT IN and the NULL trap

The NOT IN trap: if the list a NOT IN subquery returns contains even one NULL, the condition is never true and the query returns no rows, with no error.

The NOT IN trap: if the list from a NOT IN subquery contains even one NULL, the condition is never true and the query returns no rows. There is no error and no warning.

+------------------+--------------+----------+--------------+ | not_in_with_null | not_in_clean | in_found | in_not_found | +------------------+--------------+----------+--------------+ | NULL | 1 | 1 | NULL | +------------------+--------------+----------+--------------+

1-- The manager_id list contains a NULL (Vikram Rao has no manager)2SELECT COUNT(*) AS not_managers3FROM employees4WHERE emp_id NOT IN (SELECT manager_id FROM employees);5 6-- Fix 1: NOT EXISTS7SELECT e.name8FROM employees e9WHERE NOT EXISTS (SELECT 1 FROM employees r WHERE r.manager_id = e.emp_id)10ORDER BY e.name;11 12-- Fix 2: remove the NULLs from the list13SELECT COUNT(*) AS not_managers14FROM employees15WHERE emp_id NOT IN (SELECT manager_id FROM employees WHERE manager_id IS NOT NULL);

+--------------+ | not_managers | +--------------+ | 0 | +--------------+ +----------------+ | name | +----------------+ | Amit Shah | | Farah Ali | | Karthik Raj | | Manoj Pillai | | Pooja Gupta | | Rohan Das | | Suresh Babu | | Tanvi Kulkarni | +----------------+ +--------------+ | not_managers | +--------------+ | 8 | +--------------+

1-- 1. EXISTS2SELECT c.name3FROM customers c4WHERE EXISTS (5 SELECT 16 FROM orders o7 JOIN order_items oi ON oi.order_id = o.order_id8 JOIN products p ON p.product_id = oi.product_id9 WHERE o.customer_id = c.customer_id10 AND p.category_id = 411)12ORDER BY c.name;13 14-- 2. IN15SELECT name16FROM customers17WHERE customer_id IN (18 SELECT o.customer_id19 FROM orders o20 JOIN order_items oi ON oi.order_id = o.order_id21 JOIN products p ON p.product_id = oi.product_id22 WHERE p.category_id = 423)24ORDER BY name;25 26-- 3. JOIN: one row per matching item27SELECT c.name28FROM customers c29JOIN orders o ON o.customer_id = c.customer_id30JOIN order_items oi ON oi.order_id = o.order_id31JOIN products p ON p.product_id = oi.product_id32WHERE p.category_id = 433ORDER BY c.name;