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:

Syntax

Mainquery
Operator ANY( subquery)

Example

For instance, we have Stu Table as follows:

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);

OUTPUT:

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:

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.

OUTPUT: 

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:

However, the query display values of more than 1.

OUTPUT: