SQL ANY & ALL

In this tutorial, we will learn how to use ANY and ALL operator using SQL.

SQL ANY and ALL Operators

• SQL ANY and ALL Operators are used with a WHERE or HAVING Clause.
• SQL ANY operator returns true if any of the subquery values satisfy the given condition.
• SQL ALL operator returns true if ALL of the subquery values satisfy the given condition.
• Both ANY and ALL operators must be preceded by the comparison operators such as ( =, <>, !=, >, >=, <, or <=).
• Both ANY and ALL operator supported by following DBMS such as MySQL, PostgreSQL, SQL Server, Oracle.

SELECT column(s)
FROM tableName
WHERE column operator ANY
(SELECT column FROM tableName WHERE CONDITION);

SELECT column(s)
FROM tableName
WHERE column operator ALL
(SELECT column FROM tableName WHERE CONDITION);

Example:

Let us consider this table "Employee" for records.

Table Name: Employee

Example of ANY Operator

For finding ANY employee salary less than 25000 using ANY operator, a query will be:

SELECT EmpName,Salary 
FROM Employee
WHERE Salary = ANY(SELECT Salary FROM Employee WHERE Salary < 25000);

Example of ALL Operator

For finding employee salary equals to 25000 using ALL operator, a query will be:

SELECT EmpName,Salary 
FROM Employee
WHERE Salary = ALL(SELECT Salary FROM Employee WHERE Salary = 25000);