• +91-9142290450

  • Hajipur, Bihar, 844101

☰
❮
❯

MySQL EXISTS

← Previous Next →

In MySQL, the EXISTS operator is used in a WHERE clause to test for the existence of rows returned by a subquery. It returns TRUE if the subquery produces one or more rows, and FALSE if the subquery returns no rows.

EXISTS is particularly useful for checking related data across tables, filtering records conditionally, and improving query efficiency when you only need to know whether a match exists.

Basic Syntax

The syntax of EXISTS is:

SELECT column1, column2
FROM table_name
WHERE EXISTS (
    SELECT 1
    FROM another_table
    WHERE condition
);

  • The subquery checks for the existence of rows matching the condition.

  • SELECT 1 or SELECT * can be used in the subquery; the value itself is irrelevant.

  • The outer query returns rows only if the subquery produces at least one row.

Simple Example

Consider two tables: students and enrollments. To find students who have enrolled in any course:

SELECT name
FROM students s
WHERE EXISTS (
    SELECT 1
    FROM enrollments e
    WHERE e.student_id = s.id
);

  • Returns students who have at least one enrollment.

  • If a student has no corresponding entry in enrollments, they are excluded.

Using NOT EXISTS

To find rows where no matching row exists, use NOT EXISTS:

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

  • Returns students who have not enrolled in any course.

  • Useful for identifying missing or incomplete relationships.

EXISTS vs IN

  • EXISTS checks for the existence of rows returned by a subquery.

  • IN checks if a column’s value is within a list of values.

Example comparison:

-- Using EXISTS
SELECT name
FROM students s
WHERE EXISTS (
    SELECT 1
    FROM enrollments e
    WHERE e.student_id = s.id
);

-- Using IN
SELECT name
FROM students
WHERE id IN (SELECT student_id FROM enrollments);

  • Both queries return similar results.

  • EXISTS is generally more efficient for correlated subqueries, especially with large datasets.

Correlated Subquery

EXISTS is often used with correlated subqueries, where the subquery references the outer query:

SELECT name
FROM students s
WHERE EXISTS (
    SELECT 1
    FROM enrollments e
    WHERE e.student_id = s.id AND e.course_id = 101
);

  • Checks if each student has enrolled in course 101.

  • The subquery is executed for each row in the outer query, making it correlated.

EXISTS with Multiple Conditions

You can combine EXISTS with other conditions using AND, OR:

SELECT name
FROM students s
WHERE EXISTS (
    SELECT 1
    FROM enrollments e
    WHERE e.student_id = s.id AND e.status = 'Active'
) AND s.age > 18;

  • Returns students older than 18 who have active enrollments.

  • Combining EXISTS with other conditions allows precise filtering.

EXISTS in DELETE and UPDATE Queries

EXISTS can also be used in DELETE and UPDATE statements to modify rows based on subquery results.

DELETE Example

DELETE FROM students s
WHERE EXISTS (
    SELECT 1
    FROM enrollments e
    WHERE e.student_id = s.id AND e.status = 'Inactive'
);

  • Deletes students who have inactive enrollments.

UPDATE Example

UPDATE students s
SET status = 'Alumni'
WHERE EXISTS (
    SELECT 1
    FROM enrollments e
    WHERE e.student_id = s.id AND e.course_id = 101
);

  • Updates students’ status to Alumni if they have taken course 101.

EXISTS with JOIN Alternatives

EXISTS can sometimes replace JOINs, particularly when you only need to check existence, not return joined data:

-- Using JOIN
SELECT DISTINCT s.name
FROM students s
JOIN enrollments e ON e.student_id = s.id
WHERE e.course_id = 101;

-- Using EXISTS
SELECT name
FROM students s
WHERE EXISTS (
    SELECT 1
    FROM enrollments e
    WHERE e.student_id = s.id AND e.course_id = 101
);

  • EXISTS can be more efficient for large datasets because it stops checking after finding the first match.

Important Points

  1. EXISTS returns TRUE or FALSE based on subquery results.

  2. The subquery can be correlated or independent.

  3. SELECT 1 is preferred in subqueries because the returned value is irrelevant.

  4. EXISTS is often faster than IN for large correlated datasets.

  5. Use NOT EXISTS to filter for missing relationships.

  6. EXISTS can be used in SELECT, UPDATE, and DELETE queries.

Practical Scenarios

  1. Find students with enrollments:

SELECT name
FROM students s
WHERE EXISTS (
    SELECT 1
    FROM enrollments e
    WHERE e.student_id = s.id
);

  1. Find students without enrollments:

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

  1. Check enrollment in a specific course:

SELECT name
FROM students s
WHERE EXISTS (
    SELECT 1
    FROM enrollments e
    WHERE e.student_id = s.id AND e.course_id = 101
);

  1. Delete students with inactive enrollments:

DELETE FROM students s
WHERE EXISTS (
    SELECT 1
    FROM enrollments e
    WHERE e.student_id = s.id AND e.status = 'Inactive'
);

  1. Update status based on course completion:

UPDATE students s
SET status = 'Alumni'
WHERE EXISTS (
    SELECT 1
    FROM enrollments e
    WHERE e.student_id = s.id AND e.completed = 1
);

Summary of the Tutorial

  • The EXISTS operator checks for the presence of rows returned by a subquery.

  • Use NOT EXISTS to find rows with no corresponding data.

  • EXISTS is often used with correlated subqueries for precise filtering.

  • EXISTS can replace JOINs when only existence needs to be verified.

  • Efficient for large datasets, especially when only the presence of a match matters.

Mastering EXISTS allows you to write efficient queries that check relationships across tables without retrieving unnecessary data.

Practice Questions

  1. Retrieve all students who have at least one enrollment in the enrollments table.

  2. Retrieve all students who do not have any enrollments.

  3. Retrieve students who are enrolled in course_id = 101.

  4. Delete all students who have inactive enrollments.

  5. Update the status of students to 'Alumni' if they have completed any course.

  6. Retrieve students who have enrolled in courses with status 'Active'.

  7. Retrieve employees who have at least one project assigned in the projects table.

  8. Retrieve students who do not have enrollments in course_id = 105.

  9. Count the number of students who have any enrollment in the last 6 months.

  10. Retrieve courses where at least one student scored above 90.

← Previous Next →

Try a Short Quiz.

coding learning websites codepractice

No quizzes available.

MySQL

online coding class codepractice

Introduction to MySQL

MySQL SQL Basics

MySQL Functions & Operators

MySQL Joins & Unions

MySQL Grouping & Filtering

Database & Table Management

MySQL Constraints

Date & Views

References

codepractice logo
Login
Go Back Top