Oracle: All Operator

You all are advised to have a basic knowledge about Subqueries to get a clear vision about, How All operator in Oracle works? 

All Operator in Oracle is a boolean type of operator that results in true, only when all the values in subqueries return True. All operators have a different meaning when used with the < and > sign. Follow the table below to understand the different meanings.

Syntax

Mainquery 
Operator ALL Subquery

Example

You have Stu Table in the database as shown below:

< ALL Operator

This operator use to results those values that are less than the minimum value of the subquery.

Select * from Stu
where Roll_no<ALL(Select Roll_no from Stu where Stu_id<3)

The above subquery Select Roll_no from Stu where Stu_id<3 displays output as:

Now, < ALL will select the minimum values from the subquery i.e 16. However,logic of the subquery will change as:

Select * from Stu
where Roll_no<16

OUTPUT:       

> ALL Operator

The use of this used to return values that are more than the maximum value of the subquery.

Select * from Stu
where Roll_no>ALL(Select Roll_no from Stu where Stu_id>1)

Note, that the above subquery Select Roll_no from Stu where Stu_id>1 displays output as:

However >ALL operator will return values that  have Roll_No more than 17. 

OUTPUT: