Re: Difference between ON and WHERE in JOINs

On 20/09/12 03:08, Merlin Moncure wrote:
> 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
>
>
> -- Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
Respected Sir,

You example is obvious!

... to me, only after I had spent ten minutes looking at it!

Thanks,
Gavin