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 filtertable1
and table2
are the two tables to compareOPERATOR
is any SQL operator to connect the two queriesANY
compares table1
and table2
to see if there are any matchesNote: The column
placeholder can stand for multiple different columns from the two tables.
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: ANY in SQL
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.
Example: ANY in SQL
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 filtertable1
and table2
are the two tables to compareOPERATOR
is any SQL operator to connect the two queriesALL
compares table1
and table2
to see if all the values matchNote: The column
placeholder can stand for multiple different columns from the two tables.
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.
Example: ALL in SQL