Interested to Buy Any Domain ? << Click Here >> for more details...
What is the difference between IN and Exists in Oracle?
- 4 Answers
- 18433 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 |
What does refresh system mean, and what are its distinctive choice?
how may sources can be used in a mapping at a time?(limit)
What is an expression transformation in informatica?
source : xml file target: xml file how can we check data loaded into target xml file using writing sql query. pls help on this asap.
0 Answers Cap Gemini, Cognizant,
what is the size of your data warehousing?
what is mean by flag 4 in update strategy t/r
How to handle decimal places while importing a flatfile into informatica?
Parameter and variable differences
Update strategy transformation is an active transformation.How it changes the number of records that pass through it? Please explain....
If I am having 6 flat files in data. How can you load the data at a time
What are the conditions needed to improve the performance of informatica aggregator transformation?
What is the difference between SOURCE and TARGET BASED COMMITS?
Teradata 
