MySQL EXISTS

Last updated: October 19, 2026Oct 19, 2026

EXISTS tests whether a sub-query returns at least one row. It answers yes/no questions about related data: which courses have students?, which students have no enrolments? Its opposite, NOT EXISTS, is the safest way to find rows that have no match.

1. Syntax

SELECT ... FROM outer_table o
WHERE [NOT] EXISTS (SELECT 1 FROM inner_table i WHERE i.key = o.key);

• The sub-query is usually correlated: it refers to the outer row (o.key).
• Only whether a row exists matters, so SELECT 1, SELECT * or SELECT NULL all behave the same.
• EXISTS is never NULL: it is TRUE or FALSE.

2. Rows That Have a Match

SELECT c.course_id, c.title
FROM courses c
WHERE EXISTS (SELECT 1 FROM enrolments e WHERE e.course_id = c.course_id);

Result: 3 rows

COBOL is included because Ira enrolled in it once. Add conditions to the sub-query to be more specific, for example only 2026 enrolments:

SELECT c.title
FROM courses c
WHERE EXISTS (SELECT 1 FROM enrolments e
              WHERE e.course_id = c.course_id AND e.enrolled_on >= '2026-01-01');

Result: 2 rows

3. Rows Without a Match: NOT EXISTS

SELECT s.name
FROM students s
WHERE NOT EXISTS (SELECT 1 FROM enrolments e WHERE e.student_id = s.student_id);

Result: 1 row

SELECT c.title
FROM courses c
WHERE c.active = TRUE
  AND NOT EXISTS (SELECT 1 FROM enrolments e WHERE e.course_id = c.course_id);

Result: 1 row

4. EXISTS Never Duplicates Rows

Kush has two enrolments. A JOIN returns him twice; EXISTS returns each student once:

SELECT s.name FROM students s JOIN enrolments e ON e.student_id = s.student_id WHERE s.city = 'Pune';

JOIN: duplicates

SELECT s.name FROM students s
WHERE s.city = 'Pune'
  AND EXISTS (SELECT 1 FROM enrolments e WHERE e.student_id = s.student_id);

EXISTS: one row each

5. NOT EXISTS vs NOT IN with NULLs

Suppose a leads table records where prospective students came from, and one walk-in lead has no student ID yet (NULL). Which students are not leads?

CREATE TABLE leads (student_id INT NULL, source VARCHAR(10));
INSERT INTO leads VALUES (10, 'ads'), (NULL, 'walk-in');

SELECT name FROM students WHERE student_id NOT IN (SELECT student_id FROM leads);

NOT IN: nothing, because of the NULL

SELECT name FROM students s
WHERE NOT EXISTS (SELECT 1 FROM leads l WHERE l.student_id = s.student_id);

NOT EXISTS: correct answer

6. EXISTS vs IN vs JOIN

The LEFT JOIN form of the anti-join, for comparison:

SELECT s.name FROM students s
LEFT JOIN enrolments e ON e.student_id = s.student_id
WHERE e.student_id IS NULL;

Result: 1 row

7. EXISTS in UPDATE and DELETE

UPDATE courses c
SET active = FALSE
WHERE NOT EXISTS (SELECT 1 FROM enrolments e
                  WHERE e.course_id = c.course_id AND e.enrolled_on >= '2026-01-01');
SELECT title, active FROM courses;

Result: 4 rows

DELETE FROM students
WHERE NOT EXISTS (SELECT 1 FROM enrolments e WHERE e.student_id = students.student_id);
SELECT name FROM students;

Result: 3 rows

8. Performance Tips

• Index the column the sub-query filters on (here enrolments.course_id and student_id) so each check is a quick index lookup.
• MySQL 8 converts most EXISTS and IN sub-queries into semi-joins and NOT EXISTS into anti-joins; check with EXPLAIN.
• Put selective conditions inside the sub-query, so it finds or rules out a match quickly.

9. Practice Exercises

SELECT name FROM students s
WHERE EXISTS (SELECT 1 FROM enrolments e WHERE e.student_id = s.student_id AND e.score > 90);

SELECT title FROM courses c
WHERE NOT EXISTS (SELECT 1 FROM enrolments e WHERE e.course_id = c.course_id AND e.score IS NULL);

10. Summary

• EXISTS (sub-query) is TRUE if the sub-query returns any row; it is never NULL.
• Correlated sub-queries refer to the outer row; the select list inside doesn't matter.
• NOT EXISTS finds rows without a match and, unlike NOT IN, is safe with NULLs.
• EXISTS returns each outer row once; use JOIN when you need the other table's columns.
• Index the columns used inside the sub-query.