For me, the advantage is simply (1) Readability (you explicitly specify which table is joined with which other table), and (2) Support for outer joins; if you later see that you need an outer join after all, if you had a JOIN in the first place, all you have to do is change the JOIN to a LEFT JOIN; if you had a WHERE, you will have to do a major rewrite.

>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