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 |
What is Data Caches size?
write sql query following table pname description lux soap sunsilk shampoo lux shampoo sunsilk soap clouseup soap closeup paste i want the output like pname description lux soap sunsilk shampoo lux soap sunsilk shampoo closeup paste closeup paste
What is the need of an ETL tool?
What is update strategy transform?
scd methodology?
What type of sorting algorithm does the Sorter Transformation use, to do its sorting Operation? 1. Bubble sort 2. Insertion sort 3. Shell sort 4. Merge sort 5. Heapsort 6. Quicksort 7. Bucket sort
COL1,COL2 ABC,1 XYZ,2 HERE IN COL2 VALUES 1,2 NOT STSANDARD(IE MEANS NOT FIXED VALUES LIKE OTHER SOME VALUES LIKE 10,20) O/P IS COL1,COL2 ABC,2 XYZ,1
How to load query faster,so that It will take less time to load?
what are the best practices to extract data from flat file source which are bigger than 100 mb memory?
If a workflow stops or fails after loading 10k records ? How can you recover it and If there is no order while reading data from source ?
What are the limitations of joiner transformation?
If we have 1000 rows were loaded in to datawarehouse and next day the source have 1010 rows so, how can we loaded only updated rows into target.