use of IN/ANY/ALL
Answers were Sorted based on User's Feedback
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 |
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 |
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 |
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 |
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 |
Explain how exception handling is done in advance pl/sql?
Can we use threading in pl/sql?
Can you call pl/sql package functions from within a fast formula?
I want to create synonym for table emp but in my pc it is giving insufficient previliges.I am using user scott.Please suggest me.
How many triggers can be applied on a table?
Can sql developer connect to db2?
What is difference between select statement and cursor
Does sql between include endpoints?
can sql servers linked to other servers like oracle? : Sql dba
What is number function in sql?
How toimport .dmp file in lower version of oracle from higher version ?
What is procedure explain with program?