Oracle: Any or Some Operator

While moving ahead with Oracle topics, It is really important to know that there are two most important topics that you need to know and have a clear idea about how it works. Subqueries and Joins are the concepts, that are used to deal with complex business queries. 

To gain knowledge about different joins click here

Before jumping into Any Operator, you all are advised to gain knowledge about Multilrow Subqueries. Click here to gain knowledge about multirow subqueries.  

What is ANY Operator in Oracle?

In oracle, Any or Some operator is the kind of operator that returns Boolean values. It results in True if any value in the preceding subquery returns True. Any operator can be used with =,< and > comparison Operator and mean as:

=ANYThis Operator is the same as that of IN Operator in Oracle
>ANYThis Operator result in values more than the minimum value
<ANYThis Operator result in values less than the maximum value


Operator ANY( subquery)


For instance, we have Stu Table as follows:
Stu Table

1) = ANY Operator

This Operator works the same as that of IN Operator. It matches if any values match the values of the subquery.

Select * from Stu
where Roll_no= ANY(Select Roll_no from Stu where Stu_id>1);



2). > ANY Operator

This operator displays those values that are less than the maximum values of the subquery. Let's understand the Operator stepwise:

Select * from Stu
where Roll_no<ANY(Select Roll_no from Stu where Stu_id>1)

1) The subquery Select Roll_no from Stu where Stu_id>1 displays output as:
less than maximum

The maximum value of the subquery is 17. So, applying this operator will display all the values less than 17. However, the logic of the query will change to Select * from Stu where Roll_no< 17.


 less than maximum

3). < Any Operator

This operator displays those values that are more than the minimum value of the subquery.

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

The output of the subquery will be:
more than minimum

However, the query display values of more than 1.


more than minimum