Understanding ANY and ALL Operators in SQL: A Comprehensive Guide

The ANY and ALL operators are used with a WHERE or HAVING clause.

The ANY operator returns true if any of the subquery values meet the condition.

The ALL operator returns true if all of the subquery values meet the condition.

Syntax: ANY

SELECT column_name(s)
FROM table_name
WHERE column_name operator ANY
(SELECT column_name FROM table_name WHERE condition);

Syntax: ALL

SELECT column_name(s)
FROM table_name
WHERE column_name operator ALL
(SELECT column_name FROM table_name WHERE condition);

Note: The operator must be a standard comparison operator (=, <>, !=, >, >=, <, or <=).

Example: ALL

Code:

SELECT EmpName, EmpAddress
FROM Employee
WHERE EmpId = ANY (SELECT EmpId FROM Employee WHERE EmpAddress = 'Kolkata')

Output:

The above code will produce the following result-

EmpName	EmpAddress
Rambo	Kolkata
Rohit	Kolkata
Rohon	Kolkata
Ronok	Kolkata
Rubin	Kolkata
Sorif	Kolkata
Soriful	Kolkata
Sofik	Kolkata

Example: ANY

Code:

SELECT EmpName, EmpAddress
FROM Employee
WHERE EmpId = ANY (SELECT EmpId FROM Employee WHERE EmpName = 'Rambo')

Output:

The above code will produce the following result-

EmpName	EmpAddress
Rambo	Kolkata