What is the exact difference b/w IN and EXIST in Oracle..?

Question Posted / rakeshm4119

4 Answers
7822 Views
Wipro, I also Faced
E-Mail Answers

Answers were Sorted based on User's Feedback

What is the exact difference b/w IN and EXIST in Oracle..?..

EXIST is used as conditional operator in which u can write a query
IN is also used as a conditional operator in which we gives a list of elements

Is This Answer Correct ?

4 Yes

1 No

What is the exact difference b/w IN and EXIST in Oracle..?..

Answer / moorthy g

select ename from emp e
where mgr in (select empno from emp whereename = 'KING');

Here's the EXPLAIN PLAN for this query:

OBJECT OPERATION
---------- ----------------------------------------
SELECTSTATEMENT()
NESTEDLOOPS()
EMP TABLEACCESS(FULL)
EMP TABLEACCESS(BY INDEX ROWID)
PK_EMP INDEX(UNIQUESCAN)

This query is virtually equivalent to this:

select e1.ename from emp e1,(select empno from empwhere
ename = 'KING') e2
where e1.mgr = e2.empno;

You can write the same query using EXISTS bymoving the outer
query column to a subquery condition, likethis:

select ename from emp e
where exists (select 0 from emp wheree.mgr = empno and
ename = 'KING');

When you write EXISTS in a where clause, you'retelling the
optimizer that you want the outer query to be runfirst,
using each value to fetch a value from the inner
query(think: EXISTS = outside to inside).

Is This Answer Correct ?

2 Yes

0 No

What is the exact difference b/w IN and EXIST in Oracle..?..

Answer / sujeetha

It is almost similar to IN but the difference is that in
EXIST it will retrive the row atleast one row in the inner
query satisfies but in IN we can have manuy values

Is This Answer Correct ?

1 Yes

0 No

What is the exact difference b/w IN and EXIST in Oracle..?..

Answer / pavan_615

IN- sub Query - it will be executed once for the full query
Exist - Co-related sub Query - it will be executed for each
row.-- exist will me faster when you are working with big
tables.

Is This Answer Correct ?

0 Yes

0 No

Post New Answer