What is the difference between IN and Exists in Oracle?

Question Posted / aruna1105

3 Answers
14535 Views
Deloitte, I also Faced
E-Mail Answers

Answers were Sorted based on User's Feedback

What is the difference between IN and Exists in Oracle?..

Answer / ram

exists improves the performance because it returns boolian
value where as 'in' is not...so exists simplyfies internal
calculations....

Is This Answer Correct ?

9 Yes

1 No

What is the difference between IN and Exists in Oracle?..

Answer / pankaj

In sql server,

WHEN YOU USE 'IN', WHILE CHECKING FOR WHERE CONDITION SQL SERVER ENGINE DOES WHOLE TABLE SCAN. IF YOU USE 'EXISTS' AS SOON AS ENGINE FINDS THE REQUIRED ROW IT WILL STOP EXECUTING QUERY AND GOING FURTHER SCANNING TABLE.

In Oracle,
EXISTS is very faster than IN (when the subquery results is very large)

IN is faster than EXISTS (when the subquery results is very small)

In Example -
select ename from emp
where mgr in (select empno from emp where ename = 'KING');

Exists Example -
Select ename from emp a where exists ( select 0/null from emp b where a.mgr=b.empno and ename='KING')

Is This Answer Correct ?

3 Yes

1 No

What is the difference between IN and Exists in Oracle?..

Answer / sri

when the condition is in a subquery then in will be used
where if there is a condition in superquery
(parent) exists will be used. Usage of IN will improve the
performance

Is This Answer Correct ?

0 Yes

10 No

Post New Answer