Follow Our FB Page << CircleMedia.in >> for Daily Laughter. We Post Funny, Viral, Comedy Videos, Memes, Vines...


use of IN/ANY/ALL

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

More SQL PLSQL Interview Questions

What is cold data?

0 Answers  


What are the advantages of sql?

0 Answers  


select 10 from dual; y its showing all the rows with 10?

1 Answers  


What is sql and db2?

0 Answers  


how to use myisamchk to check or repair myisam tables? : Sql dba

0 Answers  






Is left join and outer join same?

0 Answers  


Explain the types of joins in sql?

0 Answers  


Is sql injection illegal?

0 Answers  


How to find the count of letter "L" in HELLO

7 Answers   HP,


how to enter characters as hex numbers? : Sql dba

0 Answers  


what is a table called, if it has neither cluster nor non-cluster index? What is it used for? : Sql dba

0 Answers  


What is trigger point?

0 Answers  


Categories