What is the difference between IN and Exists in Oracle?

Answers were Sorted based on User's Feedback



What is the difference between IN and Exists in Oracle?..

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

What is the difference between IN and Exists in Oracle?..

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

What is the difference between IN and Exists in Oracle?..

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

What is the difference between IN and Exists in Oracle?..

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

Post New Answer

More Informatica Interview Questions

In a sequential batch can u run the session if previous session fails?

3 Answers  


In development project what is the process to follow for an etl developer from day1

0 Answers  


how many new transformations are introduced in 8.1 which are not available in 7.1? any new transformation in 8.6?

2 Answers   emc2,


What is informatica? What exactly are the practical uses in real world

2 Answers  


Hello, I have the below table: CityID CityName CostOfLiving 1 Mumbai 5000 1 Bangalore 4500 1 Chennai 4800 2 Vapi 6000 3 New Delhi 8000 I am passing the rows of the above table through the aggregator transformation in Informatica What happens in the following conditions: 1) I specify no group-by ports? 2) I specify a group-by on CityID without making any separate post for aggregation? Which city and costofliving will this eventually take for CityID=1? 3) Rest being same as point 2,I take the sum of cost of living.Which city will be returned for ID=1? Thanks for your help!

1 Answers  






Can we insert and update a target table without using update strategy transformation?How?

4 Answers   Syntel,


What is workflow? What are the components of workflow manager?

0 Answers  


If we use sorted ports in aggregator transformation and somehow records are not in order by format so what will happen,session fails or it succeeded with incorrect data?

1 Answers   Amdocs,


whats the option in informatica 7 version is outdated in informatica 8 version onwards.

1 Answers   iGate,


What is exact use of 'Online' and 'Offline' server connect Options while defining Work flow in Work flow ?

1 Answers  


In warehouses how many schemas are there?

0 Answers  


What the difference is between a database, a data warehouse and a data mart?

0 Answers  


Categories