Check
Re: Understanding LEFT JOIN Thread #
1042886 Message #
1042994>In a previous thread I posted a query
>
>SELECT DISTINCT pol.agentcode,pol.ctrl_num,pol.incep_date FROM frpolicy pol;
> JOIN frlocat loc ON pol.agentcode=loc.agentcode and pol.ctrl_num=loc.ctrl_num;
> WHERE pol.incep_date >={8/31/2005} AND loc.state='OR'
>
and received a response suggesting
>
You might also consider moving your loc.state='OR' up into the JOIN.
>
>I infer that means it would have been better to write the query as
>
>SELECT DISTINCT pol.agentcode,pol.ctrl_num,pol.incep_date FROM frpolicy pol;
> JOIN frlocat loc ON pol.agentcode=loc.agentcode and pol.ctrl_num=loc.ctrl_num;
> AND loc.state='OR';
> WHERE pol.incep_date >={8/31/2005}
>
>
>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.
>
>SELECT DISTINCT pol.agentcode,pol.ctrl_num,pol.incep_date FROM frpolicy pol,frlocat loc;
> WHERE pol.agentcode=loc.agentcode and pol.ctrl_num=loc.ctrl_num;
> AND pol.incep_date >={8/31/2005};
> AND loc.state='OR'
>
>As an admitted dinosaur, I've always coded using the second style. The few times I've used a JOIN, I've put only items which show the relationship between the tables into the ON clause. I felt that made the code more readable and didn't realize it might impact performance.
>
>So, in general, is there a performance gain from using JOIN rather than WHERE? Is JOIN significantly more readable than WHERE? Is there a benefit to moving the part of the WHERE clause pertaining to the JOINED table into the JOIN expression (sorry for the convoluted expression, but I think you know what I mean).
>
>Thanks to all.........Rich
If it's not broken, fix it until it is.
My Blog