What is the exact difference b/w IN and EXIST in Oracle..?
Answers were Sorted based on User's Feedback
EXIST is used as conditional operator in which u can write a query
IN is also used as a conditional operator in which we gives a list of elements
Is This Answer Correct ? | 4 Yes | 1 No |
Answer / moorthy g
select ename from emp e
where mgr in (select empno from emp whereename = 'KING');
Here's the EXPLAIN PLAN for this query:
OBJECT OPERATION
---------- ----------------------------------------
SELECTSTATEMENT()
NESTEDLOOPS()
EMP TABLEACCESS(FULL)
EMP TABLEACCESS(BY INDEX ROWID)
PK_EMP INDEX(UNIQUESCAN)
This query is virtually equivalent to this:
select e1.ename from emp e1,(select empno from empwhere
ename = 'KING') e2
where e1.mgr = e2.empno;
You can write the same query using EXISTS bymoving the outer
query column to a subquery condition, likethis:
select ename from emp e
where exists (select 0 from emp wheree.mgr = empno and
ename = 'KING');
When you write EXISTS in a where clause, you'retelling the
optimizer that you want the outer query to be runfirst,
using each value to fetch a value from the inner
query(think: EXISTS = outside to inside).
Is This Answer Correct ? | 2 Yes | 0 No |
Answer / sujeetha
It is almost similar to IN but the difference is that in
EXIST it will retrive the row atleast one row in the inner
query satisfies but in IN we can have manuy values
Is This Answer Correct ? | 1 Yes | 0 No |
Answer / pavan_615
IN- sub Query - it will be executed once for the full query
Exist - Co-related sub Query - it will be executed for each
row.-- exist will me faster when you are working with big
tables.
Is This Answer Correct ? | 0 Yes | 0 No |
which one is costliest transformation? costliest means occupying more memory. which one Aggregator,sorter,filter,etc..
Can we use parameters of parameter file in presession command
how can i invoke pmcmd in command line prompt
how we can do session partition in informatica any one explain me clearly? thanks advance.
who to split dimentions into sub dimention
How to elaborate tracing level?
What is surrogate key?
How you load distinct records into one target and duplicate records into another target?
Can you generate sequence numbers without using the sequence generator transformation?
What is data movement mode in Informatica and difference between them?
source name sal aaaa 2000 bbbb 3000 abcd 5000 Target name sal aaaa 2000 bbbb 3000 abcd 5000 total 10000 how to get total as new row and sum as 10000 thanks
I have in my source Records like 100,101,102 etc. I have router transformation which has groups like one group is empno=100, second group is empno>99. Now i want to know my source record 100 loads into which group?