Re: Difference between ON and WHERE in JOINs

On Sep 18, 2012, at 20:21, Jean-Christophe Boggio <postgresql@thefreecat.org> wrote:

> I'm looking for an article that explains the difference between these constructs IN POSTGRESQL (the rules seem to
differfrom one DB to another) : 
>
> SELECT A.*
> FROM A
> JOIN B ON a.id=b.id AND A.somefield='somevalue'
>
> and
>
> SELECT A.*
> FROM A
> JOIN B ON a.id=b.id
> WHERE A.somefield='somevalue'
>
>
> I have noticed big differences though I don't know the rules and I've been bitten several times recently. Time to
learn.
>
> Thanks,
>
> JC
>

There is no difference in your example.  Conceptually though I suggest using only table-table conditions in an ON
clauseand placing any table-value conditions into the where. 

The main time you get differences is when you use OUTER JOIN constructions since the order of filtering can affect the
finalresult.  With an inner join the order of evaluation doesn't matter since all valid results will have a record from
bothsides of the join. 

This really shouldn't be platform specific as it is the core of SQL standard.  If you want to actually show examples
with"big differences" maybe someone can explain the reason.  Otherwise the documentation is excellent to explore what
syntaxis available in PostgreSQL.  The SELECT SQL command is the defining location. 

David J.