SQL ANY and ALL

SQL ANY

SQL ANY compares a value of the first table with all values of the second table and returns the row if there is a match with any value.

For example, if we want to find teachers whose age is similar to any of the student's age, we can use

SELECT *
FROM Teachers
WHERE age = ANY (
  SELECT age
  FROM Students
);

Here, the sub query

SELECT age
FROM Students

returns all the ages from the Students table. And, the condition

WHERE age = ANY (...)

compares the student ages (returned by subquery) with the teacher's age. If there is any match, the corresponding row of the Teachers table is selected.

SQL ALL

SQL ALL compares a value of the first table with all values of the second table and returns the row if there is a match with all values.

For example, if we want to find teachers whose age is greater than all students, we can use

SELECT * 
FROM Teachers
WHERE age >  ALL (
  SELECT age
  FROM Students
);

Here, the sub query

SELECT age
FROM Students

returns all the ages from the Students table. And, the condition

WHERE age > ALL (...)

compares the student ages (returned by subquery) with the teacher's age. If the teacher's age is greater than all student's ages, the corresponding row of the Teachers table is selected.

ANY and ALL with Comparison Operators

We can use any comparison operators like =, >, <, etc. with the ANY and ALL keywords.

Let's see an example where we want teachers whose age is less than any student.

SELECT * 
FROM Teachers
WHERE age < ANY (
  SELECT age
  FROM Students
);

Here, the SQL command selects rows if age in the outer query is less than any age in a subquery.