Let me pose this related item: It seems that one way to have an inner join execute as fast as possible is to minimize the size of the input sets from which the join is derived. For example, using my example below, if there are a lot of matches ON b.somefield=a.somefield, but overall few matches WHERE a.field1="X", it seems to me that it would be most efficient to have the WHERE execute first rather than last, thus minimizing the input overhead on the join operation. Assuming that some things are known about the data (such as in this example where I know that the WHERE logic results in only a few matches), is it more efficient to first dredge a subset from set "a" into a cursor, and then join that cursor with set "b"? This approach would impose the WHERE clause first.

Is this assumption reasonable? Is the plan of attack reasonable?


>I would expect them to be the same in terms of speed. The first one is more readable because it separates JOIN and filter conditions.
>The difference only important with LEFT OUTER JOIN because conditions in ON clause are evaluated before joining tables and WHERE conditions are evaluated after. It can produce different results.
>
>The only time you would consider putting conditions in the JOIN
>
>>With the VFP SQL engine, which query below will execute faster... or will they be converted to the same query? Is there a preferred syntax? All I'm doing is taking a simple where clause and moving it to the ON clause of an inner join. Thanks very much.
>>
>>

select a.field1, a.field2, b.fieldfromb ;
>>from table1 a ;
>>inner join table2 b on b.somefield=a.somefield;
>>where a.field1="X"

>>
>>or this:
>>
>>

select a.field1, a.field2, b.fieldfromb ;
>>from table1 a ;
>>inner join table2 b on a.field1="X" AND b.somefield=a.somefield