ANY and ALL operator is used to perform a comparison between a single column value and a range of other values. Both return a boolean value as a result. ANY returns TRUE if any of the subquery values meet the condition and ALL returns TRUE if all of the subquery values meet the condition.

Syntax of ANY/ALL operator in SQL-

SELECT <column_name(s)>

FROM <table_name>

WHERE <column_name> <comparison_operator>[ANY| ALL]( sub_query);

Example of ANY operator in SQL-

SELECT ProductName

FROM Products

WHERE ProductID= ANY (SELECT ProductID FROM OrderDetails WHERE quantity=20); 

–This SQL statement will lists the ProductName if it finds Any records in the OrderDetails table has quantity =20.

Example of ALL operator in SQL-

SELECT ProductName

FROM Products

WHERE ProductID= ALL (SELECT ProductID FROM OrderDetails WHERE quantity=20); 

–This SQL statement will lists the ProductName if it finds ALL records in the OrderDetails table has quantity =20.