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.
