sistemas 95572 (1/29/2010)

---

Assuming that:

* the inner select returns a reasonable amount of rows - so it fits in memory

shouldn't the IN statement be better since the inner query executes...

mike.anderson 52709 (1/29/2010)

---

mark.ross (1/28/2010)

---

Well, depending on the index, the table...

mark.ross (1/28/2010)

---

Well, depending on the index, the table scan COULD...

mark.ross (1/28/2010)

---

Because the 'IN' clause, when run against a large number of rows could, potentially, return a large number of values to check against, whereas the 'EXISTS' clause should still...

CirquedeSQLeil (1/28/2010)

---

mike.anderson 52709 (1/28/2010)

---

mark.ross (1/28/2010)

---

One of the reasons that the query plans are identical could be that the table being selected from in the 'IN' and 'EXISTS' clause is small....

mark.ross (1/28/2010)

---

One of the reasons that the query plans are identical could be that the table being selected from in the 'IN' and 'EXISTS' clause is small. Try running...

honza.mf (1/27/2010)

---

mike.anderson 52709 (1/27/2010)

Trying this out, I still get execution plans that are on the face of it identical (they must be different, because the result sets are different); the...

honza.mf (1/27/2010)

---

mike.anderson 52709 (1/27/2010)

---

I must say, I think you can infer that that teacher_id is a PK / FK, but I'm not sure that it makes any difference. Both queries...

honza.mf (1/27/2010)

---

CirquedeSQLeil (1/27/2010)

---

honza.mf (1/27/2010)

---

I answered OK, but...

The first variant with IN can be more effective, it allways depend on data. Compare two queries without any knowledge about structure of tables,...