David,
>
>>However, this test only shows how many records where processed by query and subquery. I doesn't take in account other factors. If you run speed tests you'll find out that in VFP IN subquery is faster in most cases than EXISTS.

>I never use EXISTS, I honestly think that INNER JOIN does that much better.

I mus admit that I seldom use EXIST, but EXIST and JOIN are not compatible. The query in EXIST is isolated so it does not JOIN its result in the final queryresult.

e.g.

SELECT a.* FROM Table1 A INNER JOIN Table2 B ON a.pk = b.fk WHERE Table2.Somefield = xSomeValue

is not the same as

SELECT * FROM Table A WHERE EXISTS (SELECT * FROM Table2 B WHERE a.Pk = b.fk AND SomeField = xSomeValue)

Only the latter ensures that only zero or one record is returned for each record in Table1

>It's also my experience with large tables over a network that NOT EXISTS is much faster than NOT IN. Later tonight I'll try to create a better test.


You might be correct. I think I remember that a query in the IN clause is regarded as a seperated query (check with set talk on?) within the original query while the EXIST variant is optimized os beeing one query. I could be wrong though....

Walter,