Re: Postgres views cannot use both union and join/where

"David G. Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> writes:
> On Tuesday, October 19, 2021, Michael Lewis <mlewis(at)entrata(dot)com> wrote:
>> On Tue, Oct 19, 2021 at 3:48 PM Mithran Kulasekaran <
>> mithranakulasekaran(at)gmail(dot)com> wrote:
>>> create view template_view (id, name, description, is_staged) as
>>> select t.id,t.name, t.description, false as is_staged
>>> from template t
>>> left join template_staging ts on t.name = ts.name and ts.name is null

>> Does that work? I've only seen that type of logic written as-
>> left join template_staging ts on t.name = ts.name
>> where ts.name is null

> The are functionally equivalent, though the timing of the expression
> evaluation differs slightly.

No, not at all. Michael's version correctly implements an anti-join,
where the first version does not. The reason is that the WHERE clause
"sees" the column value post-JOIN, whereas the JOIN/ON clause "sees"
values pre-JOIN.

Assuming that the '=' operator is strict, the first query's ON clause
really reduces to constant false, so that you just get a null-extended
image of the left table. That's almost surely not what's wanted.

regards, tom lane