>In general, does it make a difference whether the filter conditions are in the >JOIN or WHERE clause? >In practice, I've always placed the filter conditions in the WHERE clause and >just used the ON portion of the JOIN clause to join the tables. > >We have some new members of our team with a MS SQL background, and they have a >preference for including filter conditions in the JOIN clause. >They say, "the predicates in the ON clause are applied to the table before the >join, whereas the WHERE clause is semantically applied to the result >of the join" and "It has to do with the record set that is available when the >WHERE clause executes. When you move more conditions into the ON >clause, you have less records (potentially) when the WHERE clause executes" >Is that true?
I've at least never heard or experienced such a difference between JOIN and WHERE, and am pretty certain I would have known if there were such a difference between these two clauses with [INNER] JOINs in Firebird 1.5 or 2.5. I am slightly less certain regarding OUTER JOINs, though I would be surprised if it mattered for performance reasons (well, I wouldn't be surprised if you mixed SQL-89 and SQL-92, i.e. implicit and explicit join, but the only possible reason I can think of for ever using implicit join is if you want your query to be unreadable). However, since the result with outer joins will differ depending on whether things are in the ON or WHERE clause, I take it that your question was mostly relating to inner joins. Another thing I do know, is that I always prefer to put all my INNER joins before OUTER joins if possible (there are cases where it is impossible). This is due to the optimizer putting all tables in whatever order it prefers before the first OUTER JOIN, but after the outer join (typically LEFT JOIN, I rarely use FULL JOIN or CROSS JOIN and never RIGHT JOIN) things come in the same order as I've specified them in the query. Whenever I write something involving MS SQL, I typically write a (pretty) simple query to get the data I want out from or into the database and do processing in code or Firebird (if I am transferring between the two databases). This is simply due to MS SQL being rather alien to me, I simply don't know it well enough to do the stuff I easily do in Firebird. Your new members may well be in the opposite situation, knowing MS SQL well, but not Firebird. Firebird and MS SQL may speak a similar language (SQL), but the way they implement it are likely to be different. HTH, Set
