Re: A fine point about OUTER JOIN semantics

Thomas Lockhart <lockhart(at)alumni(dot)caltech(dot)edu> writes:
>>>> In some cases, an outer join with WHERE restrictions reduces to
>>>> an inner join (so the qualification clauses can be consolidated).
>> I recall you having muttered something about that before, but I don't
>> see how it works. Can you give an example of an outer join that
>> reduces to an inner join?

> Hmm. This example is pretty silly, but afaik it reduces to an inner
> join:

> select i, j from t1 left join t2 using (i) where j is not null;

> (where t1 has column "i" and t2 has columns "i" and "j").

Well, I guess so, but I can't get excited about adding machinery to
detect cases like this ... are there any less-silly examples that make
a more compelling case for expending planner cycles to see if an outer
join can be reduced to an inner join?

regards, tom lane