I'm rethinking my "slightly" comment about readability. My style is to put the WHERE equivalent of the ON clauses on separate lines and use indentation when the line needs to be broken due to extra length. That makes it fairly simple for me to read my own code, but I can see where the ON enforces readability.

I also want to thank you for the example queries. I could readily see your point, but it took me a while before I really worked my through it to understand what was happening and why.

Looking forward to your next book!!!!!

.......Rich


>>Other than being more "modern", slightly(??) more readable, and having the ability to handle non-matching keys, is there a performance benefit to using JOIN rather than WHERE clauses....ie.
>
>It's easy to check the performance consequences using SYS(3054).
>
>I think putting joins in the JOIN syntax and only filters in WHERE does aid readability. In addition, as someone said, it makes the transition from inner to outer joins easier.
>
>When you do have outer joins, there are some filter conditions that need to migrate to the join conditions. This comes up when you want to filter on the "some" side of an outer join. If you put such a filter in the WHERE clause, it removes too many records. Here's a simple example:
>
>

>* Get a list of all customers and include orders in a
>* particular month.
>
>* Here's the incorrect version
>SELECT CompanyName, OrderDate;
>   FROM Customers ;
>     LEFT JOIN Orders ;
>       ON Customers.CustomerID = Orders.CustomerID ;
>   WHERE OrderDate BETWEEN {^ 1996-9-1} AND {^ 1996-9-30} ;
>   INTO CURSOR OrdersAllCustomers
>

>
>In this example, you end up with only the customers who placed an order in the specified month.
>
>

>* Here's the working version with the filter condition
>* in the join clause
>SELECT CompanyName, OrderDate;
>   FROM Customers ;
>     LEFT JOIN Orders ;
>       ON Customers.CustomerID = Orders.CustomerID ;
>          AND OrderDate BETWEEN {^ 1996-9-1} AND {^ 1996-9-30} ;
>   INTO CURSOR OrdersAllCustomers
>

>
>What happens in the first case is that you get all the customers along with their orders. Then all orders that aren't in the specified month get filtered out. That includes any records that were added by the outer join and thus have a null order date. It also removes customers who have orders, but not in that month.
>
>In the second case, the orders from other months are filtered out before doing the join. So any customer with no orders and any customer who didn't order in that month get added to the result by the outer join.
>
>< set blatant plug on >
>BTW, these examples are pulled from my new book, "Taming Visual FoxPro's SQL," which should be released later this month.
>< set blatant plug off >
>
>Tamar