Interested to Buy Any Domain ? << Click Here >> for more details...
What is the difference between IN and Exists in Oracle?
- 4 Answers
- 18115 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 / imran
The EXISTS clause is used to check the existence of rows returned by a subquery. It returns TRUE if the subquery returns one or more rows.
The IN clause is used to filter rows based on a list of values or a subquery result set. It checks if a value matches any value in a list or subquery.
| Is This Answer Correct ? | 0 Yes | 0 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 |
Two workflows are running at same time first workflow is succeeded but second workflow is failed but there is no dependencies?
how to remove staging area
What are the restrictions of union transformation?
Write the program through which the records can be updated?
Hi, I have one flatfile as target in a mapping . When i am trying to load data second time into it. The records already is in flatfile is getting override. I don't want to override existing records. Note : we can do this by implementing CDC / Incremental pool logic if target is relational . But this is flatfile. So, even i use this same technique it will override only So what is the solution ? Is there any option at session level for flatfile target ? Advance Thanks
Issue with Update override at Target table in Informatica.
hOW CAN WE DELETE A RECORD OR TRUNCATE IN TARGET TABLE USING SQL T/R ?
If I have 10 flat files with same name abc.txt files with different timestamps as source I need to load them in tgt table oracle. in between job execution fails and rows are not loaded into tgt. how can I make them load in that target even if my job fails?
What is mapping debugger?
What is data merging, data cleansing and sampling?
What are the issues you have faced in your project? How did you overcome those issues?
What are the ETL tools available in DWH?
Teradata 
