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

Also Read:

• SQL CASE
• SQL LIKE
• SQL BETWEEN
• SQL IN and NOT IN