ANY, ALL, SOME operators

If the inner-query returns only one value and the outer query can process and display values that match the condition. At present, the inner-query might not return only one value. In these cases, the equality operator is used to check for the condition that can check for utmost only one value. If more than one value is selected an Oracle required an operator which can check and display the values. To do this operation ALL, ANY or SOME operators are used.

Any Operator

Let’s consider the following query

SELECT ename,sal,deptno FROM emp WHERE sal >=ANY(SELECT AVG(sal) FROM emp group by deptno);
This query displays the employee name and salary, the department number of employees whose salary is greater than or equal to any of the values produced through the inner query. If one of these operators is not used then the Oracle generates an error message as shown as follows.

ERROR at line 1:
ORA-01427: single-row subquery returns more than one row

Some Operator

Let consider the following query
SELECT ename,sal,deptno FROM emp WHERE sal >=SOME(SELECT Sum(sal) FROM emp group by deptno);
This query works the similar way as the ANY operator. In the execution of the query there is no difference.
Note: SOME and ANY operators are equivalent to the OR operator and the IN operator.

All Operators

ALL operators are used to check for all the values returned through the list. The condition is checked for  all  the  values  and  the  records  which  match  all  the  values  is  displayed.  
Let Consider the below example:
SELECT pcode FROM ordprd WHERE qty >=ALL(SELECT AVG(qty) FROM ORDPRD GROUP BY pcode)

Output will be:

PCODE
106

This example first calculates the average qty of all the products grouped through product code and then checks for the product for that the qty is greater than or equal to the average qty. The records for that the condition is satisfied are displayed.

EXISTS OPERATOR

If inner query evaluates to TRUE the Outer query is executed.
Example
SELECT DEPTNO, DNAME, LOC FROM DEPT WHERE EXISTS (SELECT * FROM EMP)
The output looks like:

DEPTNO    DNAME           LOC
10        ACCOUNTING    NEW YORK
20        RESEARCH        DALLAS
30        SALES              CHICAGO