What is the difference between IN and Exists in Oracle?
- 3 Answers
- 14488 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 is materialized view?
My source table look like ID Name Location Salary 1 ran NULL NULL 1 NULL DELHI NULL 1 NULL NULL 1000 I want my output to look like ID Name Location Salary 1 ran Delhi 1000 What should be my approach to design a informatica mapping. Assuming my source is flat file not a Relation table so i cant use SQL.
How i can Schdule the Informatica job in "Unix Corn Schduling tool" ?
Can anyone tell me the new features in Informatica 9 Version?
What all are steps in up gradation of Informatica server?
Workflow is long running due to long running sql query so when we refer the query plan it tells the issue is due to partition of the db table. How to handle this?
is it possible to index the data in cache of lkp transformation ?
What is log file,and where is it located,why u r using the log file
Without source how to insert record to target?
What is the difference between source qualifier transformations source filter to filter transformation?
Can we use the mapping parameters or variables created in one mapping into any other reusable transformation?
What are the Differences between connected and unconnected lookup?
Teradata 
