>Hi,
>What is the difference between this 2 SQL?
>
>

>SELECT * FROM a ;
>JOIN b ON a.f1 = b.f1 ;
>JOIN c ON b.f1 = c.f1
>

>
>

>SELECT * FROM a ;
>JOIN b ;
>JOIN c ;
>ON b.f1 = c.f1 ;
>ON a.f1 = b.f1
>

>
>Thank you

Hi John,

Those queries both use nested JOIN, the only difference is on which side. Parenthesis should make it clear.

The first query (nested join on the left):

SELECT * FROM ;
(a JOIN b ON a.f1 = b.f1) JOIN c ON b.f1 = c.f1

The second query (nested join on the right):

SELECT * FROM ;
a JOIN (b JOIN c ON b.f1 = c.f1) ON a.f1 = b.f1

You may think of this like the nested JOIN is evaluated first and then its result is used to evaluate the upper level JOIN. However, in case of INNER JOINs, the result is going to be the same regardless of the order in which the join conditions are evaluated (when OUTER JOIN is involved, the order of evaluation is significant). For example, the following query returns the same result as the previous two:

SELECT * FROM a, b, c;
WHERE b.f1 = c.f1 AND a.f1 = b.f1

In fact, VFP SQL engine executes query #1 and query #2 as if they both were written using form #3. It ignores the nesting and applies special logic to determine which two tables should be joined first to achieve the best performance. In some cases, it might fail to find the best execution plan. But, there is a way to force query evaluation to follow certain plan, this is where the nesting comes to the game. If the FORCE keyword is used after the FROM keyword then SQL engine obeys the nesting and evaluates nested JOINs first, which gives control in which order the tables are joined and may allow to achieve better performance.

Thanks,
Aleksey.