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.

what is the difference between delete and truncate commands? : Sql dba

1117

---

what is the difference between char and varchar data types? : Sql dba

1173

---

What is difference between ms sql and mysql?

1015

---

Is stored procedure faster than query?

1019

---

What is the difference between execution of triggers and stored procedures?

1155

---

What are the possible values for the boolean data field?

1012

---

What are hotfixes and patches?

986

---

What are the different dml commands in sql?

1247

---

What are the different types of a subquery?

1102

---

How does pl sql work?

1006

---

what is a database lock ? : Sql dba

1225

---

How to select unique records from a table?

1109

---

How can I get the number of records affected by a stored procedure?

1118

---

How do you create a unique index?

1092

---

How do I write a cron which will run a sql query and mail the results to agroup?

1030

---