What is the difference between IN and Exists in Oracle?
- 3 Answers
- 14528 Views
- Deloitte, I also Faced
- E-Mail Answers
Answer Posted / 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 |
Post New Answer View All Answers
How can you increase the performance in joiner transformation?
How do you load only null records into target?
What is joiner transformation in informatica?
Explain sessions and how many types of sessions are there?
Is it possible to use a client with different version than that of its Informatica server?
What are the conditions needed to improve the performance of informatica aggregator transformation?
What is Session and Batches?
TELL ME ONE COMPLEX MAPPING IN UR PROJECT? chandumba2005@gmai.com
How to update a particular record in target with out running whole workflow?
What is a shortcut and copy in Informatica and how two are different with each other?
What is resilience time?
write a query to retrieve the latest records from the table sorted by version(scd)
Tell me any other tools for scheduling purpose other than workflow manager pmcmd?
How many number of sessions can one group in batches?
Difference between Target-based loading and constraint-based loading?
