Answer Posted / subathra
IN: Inner query executes first and drives the outer query.
EXISTS: Outer query executes first and compares tge rows
retrived with the inner query.
Consider table tab1 has 1000 rows and table tab2 has 1000
rows.
IN: select t1.empid from tab1 t1 where t1.code in (select
t2.code from tab2 t2) -- All rows in t1 will read with t2
and the effect is 1000 X 1000 rows.
EXISTS: select t1.empid from tab1 t1 where exists (select 1
from tab2 t2 where t1.code=t2.code) -- Max of 1 row will be
read for each row of t1 and thus reduces the processing
overhead.
Thumb rule:
1) If the majority of the filtering are in the sub query
then use IN.
1) If the majority of the filtering are in the outer query
then use EXISTS.
| Is This Answer Correct ? | 4 Yes | 0 No |
Post New Answer View All Answers
What is the use of stored procedures?
what are tables and fields? : Sql dba
What is pl sql in oracle?
What is sqlerrm?
Can we rename a column in the output of sql query?
What type of database is cloud sql?
How would you reference column values before and after you have inserted and deleted triggers?
What is pivot query?
What is the difference between the conventional and direct path loader? : aql loader
How to avoid using cursors?
What is the use of %rowtype?
How to assign sql query results to pl sql variables?
What is the sql*loader? : aql loader
What is sorting in sql?
i have 2 table table one 4 columns respective values a1 7,a2 6,a3 8 ,a4 12 & table two 4 colums respective values a1 7,a2 6,a3 8,a4 15.if table one & table two 3 colums same then 4th column values 1)Qes diff >5 then print 5 * diff value 2)Que diff <5 print 5
