multiple row subqueries

When a sub query returns more than one row then that sub query is called as multi sub query.Here i will shown an example to find out the all customers who are having high sales

select *from Orders where sales in(select max sales)from Orders group by city no)

Any ans All:with the multi row sub query it is not possible to use the operators like equal(=),greater than(>),>=,<=,<.because these operators can be used only with single values.but there may be a situation where you have to use these operators with multi row sub queries and for this two predicates are provided Any or same and All The predicate any will return true when the given condition is true with any one value returned by multi row sub query and All will return true when the given condition is true with all values returned by the multi row sub query
Here i will given an example for getting highest sales in order

select *from Orders where sales =(select max sales)from Orders group by city no
)

Find out the emp who are not working in dept30 and whose Sal is more than any one employee working dept 30

select *from emp where deptno!=30 and Sal>Any (select distinct Sal from emp where dept no=30)
)