SV: [firebird-support] filter conditions: WHERE vs. JOIN clause
Hello Ann Can you please explain better that phrase? With outer joins, a condition in the WHERE clause that affects the right side of a left outer join (the one that doesn't have to exist) effectively turns off the outerness of the join (unless it includes OR value IS NULL). I think this is very simple, Walter, Ann is just saying that a) SELECT P.Name, C.Mayor FROM PERSONS P LEFT JOIN CITIES C ON P.CITY = C.CITY WHERE P.FIRST_NAME = 'Walter' AND C.COUNTRY = 'Laos' would only return the persons residing in Laos (outerness turned off), whereas b) SELECT P.Name, C.Mayor FROM PERSONS P LEFT JOIN CITIES C ON P.CITY = C.CITY AND C.COUNTRY = 'Laos' WHERE P.NAME = 'Walter' will return all persons named Walter regardless of where they live (the mayor of the town being included if the person lives in Laos). SELECT P.Name, C.Mayor FROM PERSONS P LEFT JOIN CITIES C ON P.CITY = C.CITY WHERE P.FIRST_NAME = 'Walter' AND (C.COUNTRY = 'Laos' or C.COUNTRY IS NULL) is an example of the exception that Ann notes (unless it includes OR value IS NULL) Admittedly, the above example makes no sense, but has its uses in other cases. The way I most commonly use IS NULL with LEFT JOIN will be a variation of b): SELECT P.Name FROM PERSONS P LEFT JOIN CITIES C ON P.CITY = C.CITY AND C.COUNTRY = 'Laos' WHERE P.NAME = 'Walter' AND C.COUNTRY IS NULL This basically gives you the name of all Walters excepting those that live in Laos (it is an alternative to using NOT EXISTS). HTH, Set
[firebird-support] filter conditions: WHERE vs. JOIN clause
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? Thank you.
Re: [firebird-support] filter conditions: WHERE vs. JOIN clause
On 27/05/2014 16:17, jakef...@yahoo.com [firebird-support] wrote: In general, does it make a difference whether the filter conditions are in the JOIN or WHERE clause? In particular, if there are outer joins you can get different results. -- Tim Ward
Re: [firebird-support] filter conditions: WHERE vs. JOIN clause
Jake, I've a background in MS Sql also. Had to look this up before answering, but I don't think your new members know as much as they think they know. In MsSql, if the sql statement is written correctly there is no consistent gain doing it one way or the other. The optimizer sees to that. Frankly, I think you should stay with the traditional way of doing things, that is put the filter in the where clause. Its what is expected and easier for those who follow to read and understand -- Dixon Epperson
SV: [firebird-support] filter conditions: WHERE vs. JOIN clause
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
Re: [firebird-support] filter conditions: WHERE vs. JOIN clause
On Tue, May 27, 2014 at 11:17 AM, jakef...@yahoo.com [firebird-support] firebird-support@yahoogroups.com wrote: 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? Not for Firebird, and probably not for MS SQL either. For inner joins, the optimizer picks the join order that minimizes the number of rows to be retrieved based on all conditions the JOIN ON terms and the WHERE terms. Before doing so, Firebird distributes equalities, so if you have a multi-way join on a single term (e.g. customer.cust_id = invoice.cust_id for the first pair and invoice.cust_id = payment.cust_id on the second) Firebird can join any of the pairs. With outer joins, a condition in the WHERE clause that affects the right side of a left outer join (the one that doesn't have to exist) effectively turns off the outerness of the join (unless it includes OR value IS NULL). It's a pretty pathetic join optimizer that doesn't move conditions around and depends on the user's syntax to order conditions. Logically, you can think about the operation as doing the joins in the order presented on the conditions in the JOIN ... ON, then applying the WHERE conditions to that output stream, but one of the nice things about relational databases is that the engine is free to do anything behind the scenes to make your query fast in spite of your efforts. Good luck, Ann
