On 6/19/06, MB Software Solutions <[EMAIL PROTECTED]> wrote:
Mathematically, it would make sense to exclude records in the JOIN phase first, and then discriminate later in the WHERE clauses, imo.
Assuming the process is serial: that the computer reads from left-to-right, top-to-bottom, and executes the SQL as read. In most modern SQL processors, there are several steps where the statement is parsed, re-organized and optimized. Table structures are examined, index expressions are evaluated, retrieval time calculated and different plans are testing and weighed against each other. Depending on the sophistication of the optimization step, order shouldn't matter, as the SQL processor should find the optimal order of processing. However, often "hints" are needed to tell the processor which indexes to use or which order to process the joins or filters in. VFP's Rushmore has a fairly lightweight cost-based optimizer. It creates temporary tags on fields if the cost of building the index is thought to be smaller than the cost of scanning all rows in the table. It processes tables in pairs in the order given . (At least it did when I looked at this in depth a couple of versions ago; the Fox team has tweaked performance in every version so things can change.) ote that this is a very different discussion than the initial question about filtering on an outer join: you ought to have your filters on the nullable side of the join in the ON clause, as that's where VFP decides how to join the tables and which rows get null values. I'm away from my library at the moment, but I'd be curious if Tamar has something about this on her SQL book: http://www.hentzenwerke.com/catalog/tamingvfpsql.htm I don't know of a downside to adding filter expressions to the ON clauses, but I'd welcome a counter-example. -- Ted Roche Ted Roche & Associates, LLC http://www.tedroche.com _______________________________________________ Post Messages to: ProFox@leafe.com Subscription Maintenance: http://leafe.com/mailman/listinfo/profox OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
