Re: Difference between ON and WHERE in JOINs

On Sep 18, 2012, at 20:21, Jean-Christophe Boggio <postgresql(at)thefreecat(dot)org> wrote:

> I'm looking for an article that explains the difference between these constructs IN POSTGRESQL (the rules seem to differ from 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 clause and 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 final result. With an inner join the order of evaluation doesn't matter since all valid results will have a record from both sides 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 syntax is available in PostgreSQL. The SELECT SQL command is the defining location.

David J.