ANY / ALL

Overview

ANY / ALL — Compare a Value to a List of Values

ANY and ALL are used when you want to compare one value to many values returned by a subquery.

They help answer questions like:

• “Is this value bigger than any value in that list?”
• “Is this value bigger than all values in that list?”

The code stays simple — the explanation becomes clearer.

---

What ANY Means

ANY means:

The condition is TRUE if it matches at least one value in the subquery.

Think of it like checking a list:

• If any item in the list makes the comparison true → the whole condition is true

Example thought process:

“Is 3 greater than ANY of these numbers: 2, 5, 7?”
→ Yes, because 3 > 2

---

What ALL Means

ALL means:

The condition is TRUE only if it matches every value in the subquery.

Think of it like:

• If every item in the list makes the comparison true → the whole condition is true

Example thought process:

“Is 8 greater than ALL of these numbers: 2, 5, 7?”
→ Yes, because 8 > 2, 8 > 5, and 8 > 7

---

The Basic Patterns

ANY

value operator ANY (subquery)
  

ALL

value operator ALL (subquery)
  

---

Examples

Example 1: ANY — Courses with credits greater than ANY Math course

SELECT course_name,
       credits
FROM courses
WHERE credits > ANY (
    SELECT credits
    FROM courses
    WHERE department = 'Math'
);
  

Explanation (Beginner Friendly)

• The subquery returns a list of credit values for Math courses
• credits > ANY (...) means:

“Is this course’s credit value greater than at least one Math course?”

Even if Math has credits 2, 3, 4 → a course with 3 credits qualifies (because 3 > 2).

---

Example 2: ANY — Students with student_id greater than ANY instructor_id

SELECT first_name,
       last_name,
       student_id
FROM students
WHERE student_id > ANY (
    SELECT instructor_id
    FROM instructors
);
  

Explanation

• The subquery returns all instructor IDs
• student_id > ANY (...) means:

“Is this student’s ID greater than at least one instructor ID?”

If even one instructor has a smaller ID → the student qualifies.

---

Example 3: ALL — Courses with credits greater than ALL Math courses

SELECT course_name,
       credits
FROM courses
WHERE credits > ALL (
    SELECT credits
    FROM courses
    WHERE department = 'Math'
);
  

Explanation

• The subquery returns all Math course credits
• credits > ALL (...) means:

“Is this course’s credit value greater than every Math course?”

If Math has credits 2, 3, 4 → only courses with credits greater than 4 qualify.

---

Example 4: ALL — Students with student_id less than ALL instructor_id values

SELECT first_name,
       last_name,
       student_id
FROM students
WHERE student_id < ALL (
    SELECT instructor_id
    FROM instructors
);
  

Explanation

• The subquery returns all instructor IDs
• student_id < ALL (...) means:

“Is this student’s ID smaller than every instructor ID?”

If even one instructor has a smaller ID → the student does not qualify.

---

ANY vs ALL (Beginner Summary)

Operator	Meaning	Easy Explanation
ANY	TRUE if comparison matches at least one value	“Is this true for at least one?”
ALL	TRUE if comparison matches every value	“Is this true for everyone?”

---

Practice Tasks (Your Turn!)

Task 1 (ANY)

Show courses whose credits are greater than ANY credits in the Sociology department.

Task 2 (ANY)

Show students whose student_id is greater than ANY student_id found in the enrollments table..

Task 3 (ALL)

Show courses whose credits are greater than ALL credits in the Sociology department.