MySQL EXISTS Operator

The EXISTS operator in MySQL is used in combination with a subquery. It returns true if the subquery returns at least one record and false if no rows are returned. The EXISTS operator is often faster than IN when the subquery results in a large number of results.

Syntax 

Here's the basic syntax for the EXISTS operator in MySQL:

SELECT column_name(s)
FROM table_name
WHERE EXISTS (subquery);

Demo Database

Consider the following tables in our demo database:

Examples

Find students who have taken at least one course:

SELECT student_name 
FROM students 
WHERE EXISTS (
    SELECT 1 
    FROM courses 
    WHERE students.student_id = courses.student_id
);

Output:

Alice
Bob
Charlie

Find students who haven't taken any courses:

SELECT student_name 
FROM students 
WHERE NOT EXISTS (
    SELECT 1 
    FROM courses 
    WHERE students.student_id = courses.student_id
);

Output:

David

Find students who have taken 'Mathematics':

SELECT student_name 
FROM students 
WHERE EXISTS (
    SELECT 1 
    FROM courses 
    WHERE students.student_id = courses.student_id AND course_name = 'Mathematics'
);

Summary

The EXISTS operator in MySQL is a Boolean operator that returns true or false. It's used with a subquery, and it's great for situations where you need to check the presence of records based on certain conditions.

Instead of fetching the whole result set, like the IN operator does, EXISTS simply returns true if a matching record is found and stops scanning the rest of the data. This makes the EXISTS operator efficient and a good choice when performance is a concern.