What is the difference between IN and Exists in Oracle?
- 3 Answers
- 14516 Views
- Deloitte, I also Faced
- E-Mail Answers
Answers were Sorted based on User's Feedback
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 |
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 |
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 |
What does role playing dimension mean?
While migrating the data from one environment to another environment how would you manage the connections?
What is ThroughPut in Informatica, How it works, Where I can find this option to check ?
What are the parts of Informatica Server?
What is difference between Informatica 6.2 Workflow and Informatica Workflow 7.1
my source is junk data how will u remove that junk data by using unix please any one replay me
i have a table with name field. i,e name Shankar prabhakar nitikripa so no if a occures 3 times in name then it will go to tgt A if b occures 3 times in name then it will go to tgt b .. if z occures 3 times in name then it will go to tgt z
After draging the ports of three sources(sql server,oracle,informix) to a single source qualifier, can you map these three ports directly to target?
How can we get multiple output values using an unconnected lookup transformation?
can v update d records in target using update stargey without generationg primary key ? explain
Hi, I am new to Informatica, What is a flat file and how to use flat file in infomratica please help me.
How many ways are there to do 'remove duplicate records in informatica'?
Teradata 
