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 view and stored procedure?

959

---

How do I add a primary key to a table?

1042

---

what is the stuff function and how does it differ from the replace function? : Sql dba

1045

---

What are pl sql data types?

1009

---

How many types of normalization are there?

959

---

Explain the difference in execution of triggers and stored procedures?

1022

---

Is sql database free?

973

---

How do you optimize a query?

998

---

what is blob? : Sql dba

1148

---

What is an intersect?

1102

---

Define commit, rollback and savepoint?

1025

---

Advantages and disadvantages of stored procedure?

1122

---

What is a schema? How is it useful in sql servers?

1119

---

how many sql dml commands are supported by 'mysql'? : Sql dba

1120

---

How do I add a database to sql?

1125

---