EXISTS

Overview

EXISTS — Check If Related Data Exists

EXISTS is used in the WHERE clause to check whether at least one matching row exists in another table.

It does not return data from the subquery.
It only returns TRUE or FALSE.

This makes it perfect for questions like:

• Does this student have any enrollments?
• Does this course have any students?
• Does this department offer any courses?

---

The Basic Pattern

SELECT columns
FROM table_name
WHERE EXISTS (
    SELECT 1
    FROM other_table
    WHERE condition
);
  

• The subquery runs for each row of the outer query
• If the subquery finds at least one row → TRUE
• If it finds none → FALSE

---

Examples

Example 1: Students who have at least one enrollment

SELECT s.first_name,
       s.last_name
FROM students s
WHERE EXISTS (
    SELECT 1
    FROM enrollments e
    WHERE e.student_id = s.student_id
);
  

This returns only students who appear in the enrollments table.

Example 2: Courses that have at least one student

SELECT c.course_name
FROM courses c
WHERE EXISTS (
    SELECT 1
    FROM enrollments e
    WHERE e.course_id = c.course_id
);
  

Courses with no enrollments are filtered out.

Example 3: Instructors whose department offers at least one course

SELECT i.first_name,
       i.last_name,
       i.department
FROM instructors i
WHERE EXISTS (
    SELECT 1
    FROM courses c
    WHERE c.department = i.department
);
  

This checks whether the instructor’s department appears in the courses table.

---

Why EXISTS Is Useful

• Very readable for beginners
• Great for “does this related data exist?” questions
• Often faster than IN on large tables
• Works perfectly with correlated subqueries

---

EXISTS Summary

Concept	Meaning
EXISTS	TRUE if the subquery returns at least one row
Best for	Checking related data
Returns	Only TRUE / FALSE (not data)

---

EXISTS is like checking a classroom.
You don’t need to know who is inside — just whether anyone is there 🏫

---

Practice Tasks (Your Turn!)

Task 1

Show students who have at least one enrollment.

Task 2

Show courses that have at least one student.

Task 3

Show instructors whose department appears in the courses table.