Merlin Moncure <mmoncure@xxxxxxxxx> 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
