use of IN/ANY/ALL

Categories >> Software >> Databases >> SQL PLSQL
Suggest New Category

use of IN/ANY/ALL

Question Posted / senthilsjc

5 Answers
10976 Views
Ramco, I also Faced
E-Mail Answers

Answers were Sorted based on User's Feedback

use of IN/ANY/ALL..

Answer / bunty

IN - used to select multiple rows based on any of the key
provided

SQL - select distinct employeeid from orders where orderid
in ( select orderid from orderdetails where discount >= 10)

ANY - used in case of relational queries to compare result
with any of the key.

SQL - select custID from orders where regionID != "E" and
discount > any (select discount from orders where regionID
= "E" and discount > 3)

ALL - used in case of relational queries to compare result
with all of the keys.

SQL - select custID from orders where regionID != "E" and
discount > all (select discount from orders where regionID
= "E" and discount > 3)

Cheers -
Bunty

Is This Answer Correct ?

11 Yes

0 No

use of IN/ANY/ALL..

Answer / s. syam sundar

IN,ANY,ALL ARE MULTIPLE OPERATORS
THESE ARE USED IN MULTIPLE ROW SUBQUERIES

Is This Answer Correct ?

7 Yes

0 No

use of IN/ANY/ALL..

Answer / satyajit patel

-> in------------ Looks to all values returned in sub-query

-> <any---------- looks to highest value in sub-query
-> >any---------- looks to smallest value in sub-query

-> <all---------- looks to smallest value in sub-query
-> >all---------- looks to highest value in sub-query

Is This Answer Correct ?

5 Yes

1 No

use of IN/ANY/ALL..

Answer / manoj

IN- It will return the value based on the parameter;
e.g. select * from emp where salary in ('1000','3000');

ANY-It will compare with any value that has been returned by
the parameter;

select * from emp where salary > any(select salary from emp
where deptno=10)
the salary will be compared with any value that has been
returned by the subquery.

ALL-It will compare with max/min value that has been
returned by the subquery;

select * from emp where salary > all(select salary from emp
where deptno=10)
the salary will be compared with the longest value that has
been returned by the subquery.

Is This Answer Correct ?

3 Yes

0 No

use of IN/ANY/ALL..

Answer / mohamed shahid(broadline)

IN:-EQUAL TO ANY MEMBER
EX:-
---
SELECT ENAME,SAL,DEPTNO FROM EMP WHERE SAL IN(SELECT MIN(SAL) FROM EMP GROUP BY DEPTNO);

ANY(<):-COMPARES VALUE TO EACH VALUE RETURNED BY THE SUBQUERY
EX:-
SELECT EMPNO,ENAME,JOB FROM EMP WHERE SAL<ANY(SELECT SAL FROM EMP WHERE JOB='CLAERK');

NOTE:-IT MEANS LESS THAN MINIMUNM VALUE.
ANY(>):-
------

SELECT EMPNO,ENAME,JOB FROM EMP WHERE SAL>ANY(SELECT SAL FROM EMP WHERE JOB='CLERK');

NOTE:-
-----
>ANY:-IT MEANS MORE THAN MINIMUM
=ANY:- IT IS EQUIVALENT TO IN OPERATOR

ALL:-COMPARES VALUE TO EVERY VALUE RETURNED BY THE SUBQUERY

SELECT EMPNO,ENAME,JOB FROM EMP WHERE SAL>ALL(SELECT AVG(SAL) FROM EMP GROUP BY DEPTNO);
NOTE:-
---
>ALL IT MEANS MORE TAHN MAXIMUM

SELECT EMPNO,ENAME,JOB FROM EMP WHERE SAL<ALL(SELECT AVG(SAL) FROM EMP GROUP BY DEPTNO);
NOTE:-
---
ALL<:-IT MEANS LESS THAN MINIMUM

Is This Answer Correct ?

1 Yes

0 No

Post New Answer