Presentation on SQL Operators: ANY and ALL

Introduction

• Focus on two different SQL operators: ANY and ALL
• Output of both operators is Boolean: true or false

ANY Operator

• Boolean Output: The condition is true if the operation is true for any of the values in the subquery.
• Syntax:

  SELECT column FROM table1 WHERE column OPERATOR ANY (SELECT column FROM table2);
  

• Supported Operators: <, <=, =, >, >=, !=

Example Query

SELECT * FROM teachers WHERE age = ANY (SELECT age FROM students);

• Execution:
  • Executes inner query first (against students table)
  • Compares each age in teachers table with any age in students
  • Returns rows where a teacher's age matches any student's age

Result Set Example

• Teachers with ages matching any student’s age (e.g., age = 32)

ALL Operator

• Boolean Output: The condition is true if all values in the subquery satisfy the condition.
• Syntax:

  SELECT column FROM table1 WHERE column OPERATOR ALL (SELECT column FROM table2);
  

• Supported Operators: Same as ANY

Example Query

SELECT * FROM teachers WHERE age > ALL (SELECT age FROM students);

• Execution:
  • Checks if a teacher's age is greater than all ages in the students table
  • Returns rows where this is true

Result Set Example

• Only returns teachers' rows where their age is greater than all students' ages (e.g., age = 43)

Homework Question

• Modify the use of ALL:

  SELECT * FROM teachers WHERE age < ALL (SELECT age FROM students);
  

• Determine if the result will include three rows or another number.
• Execution should consider all values from the subquery.

Conclusion

• Difference between ANY (one or more) vs. ALL (all must satisfy)
• Homework to solidify understanding

Appreciation

• Thank you for watching the session!