EXISTS, ANY & ALL

EXISTS checks whether a related subquery finds any row. It stops at the first match and is often efficient for 'has at least one' checks.

EXISTS — true if subquery returns rows

EXISTS checks whether a related subquery finds any row. It stops at the first match and is often efficient for 'has at least one' checks.

Real-life example: EXISTS is asking 'Is there any enrollment for this student?' — yes or no, without listing every course.

Students who have enrollments

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

ANY (or SOME) means the condition is true if it matches at least one row from the subquery. Often used with > ANY (...).

Real-life example: ANY is 'score higher than at least one passing student' — one match is enough.

Scores greater than any score in course 1

SELECT student_id, score
FROM enrollments
WHERE score > ANY (
  SELECT score FROM enrollments WHERE course_id = 1
);

ALL means the condition must be true for every value returned by the subquery. > ALL (...) means greater than the maximum of the set.

Real-life example: ALL is 'beat every score in the top class' — you must exceed them all, not just one.

Scores greater than all scores in course 3

SELECT student_id, score
FROM enrollments
WHERE score > ALL (
  SELECT score FROM enrollments WHERE course_id = 3 AND score IS NOT NULL
);