Interested to Buy Any Domain ? << Click Here >> for more details...
What is the difference between IN and Exists in Oracle?
- 4 Answers
- 18110 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 is Factless fact table ???
12 Answers Satyam, Tech Mahindra,
SOURCE 1 a 1 b 1 c 2 a 2 b 2 c TARGET 1 A B C 2 A B C In oracle & informatica level how to achieve
HOE DO U IMPLIMENT SCHEDULING IN INFORMATICA?
Hi experts, table 1 has 10 records, table 2 has 5 records select * from table1, table2; What is the output for this query???....plz answer
How to delete duplicate record in Informatica?
What is informatica?
in my lookup table i want to catch the recently updated records from the source to target,how to achieve this and what is last commit interval point for this?
Design a mapping to calculate department wise sum of salaries and load it in single target? Source Target Deptno Salary Deptno Salary 10 100 10 600 10 200 10 600 10 300 10 600 20 200 20 800 20 300 20 800 20 300 20 800 30 400 20 1500 30 500 30 1500 30 600 30 1500
select * from emp where sal>(select min(sal) from emp) how to implement the same in informatica ?
What is lookup transformation?
How can i generate Seqence Numbers to Tagret Table (with out using Seqence Gen Trans,Rank Trans).
error reading data in flatfiles. Instead rading Ravi Teja its reading Ravi?Teja.How to handle this issue.
Teradata 
