 # SQL ANY and ALL

In this tutorial, we'll learn about SQL ANY and ALL operators with the help of examples.

## 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.