Merlin Moncure <mmoncure(at)gmail(dot)com> wrote:
> 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';
Consider the hypothetical case of a person table which is vertically
partitioned to have less frequently used information stored in a
demographics table. The goal is to keep the person table narrow, so
that common uses (which don't reference any of the demographics
information) can be faster. The demographics row is only present if
one or more of the non-key values is not null. Let's say you want
to do a list which only includes people not known to be dead. If
you put the `demographics.date_of_death IS NULL` test in the ON
clause, in a manner similar to your above example, you will get
incorrect results.
I will concede that most of the time you want conditions related to
the right-hand table of a left join in the ON clause; but that's not
always the right thing to do. The question is: "Is this a condition
which should control whether data from the optional table is
included, or is this a condition which should determine whether the
joined data row as a whole is included in the result?"
-Kevin