 # SQL ANY and ALL

## SQL ANY Operator

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.

It has the following syntax:

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

Here,

• `column` is the name of the column(s) to filter
• `table1` and `table2` are the two tables to compare
• `OPERATOR` is any SQL operator to connect the two queries
• `ANY` compares `table1` and `table2` to see if there are any matches

Note: The `column` placeholder can stand for multiple different columns from the two tables.

## Example 1: SQL ANY Operator

Suppose we want to find teachers whose age is similar to any of the student's age. Then, we can use the following query:

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

Here, the subquery below returns all the ages from the `Students` table.

``````SELECT age
FROM Students``````

And, the condition below compares the student ages (returned by subquery) with the ages of the teachers.

``WHERE age = ANY (...)``

If there is any match, the corresponding row of the Teachers table is selected.

## Example 2: SQL ANY With the < Operator

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

Let's look at 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.

## SQL ALL Operator

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.

It has the following syntax:

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

Here,

• `column` is the name of the column(s) to filter
• `table1` and `table2` are the two tables to compare
• `OPERATOR` is any SQL operator to connect the two queries
• `ALL` compares `table1` and `table2` to see if all the values match

Note: The `column` placeholder can stand for multiple different columns from the two tables.

## Example 3: SQL ALL Operator

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 subquery below returns all the ages from the Students table.

``````SELECT age
FROM Students``````

And, the condition below compares the student ages (returned by subquery) with the ages of the teachers.

``WHERE age > ALL (...)``

If the teacher's age is greater than all student's ages, the corresponding row of the Teachers table is selected.