Re: Difference between ON and WHERE in JOINs

On Tue, Sep 18, 2012 at 7:47 PM, David Johnston <polobo(at)yahoo(dot)com> wrote:
> 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.

Yeah. This comes up most often with left joins. It's the source of
the #1 bug I see in SQL -- it trips up even the experts sometimes.

SELECT * FROM foo LEFT JOIN BAR ON foo.id = bar.id WHERE bar.col = 'something';

By having the filtering in the where clause, the intended purpose of
the left join, to return every row of foo, is being defeated and the
join will behave like an inner join. The right way to do it is:

SELECT * FROM foo LEFT JOIN BAR ON foo.id = bar.id AND bar.col = 'something';

The difference here is that the filtering is now happening at join
time where the left join semantics are playing: always return foo and
return bar rows if and only if the join condition is met.

merlin