From: Tom Lane <tgl@sss.pgh.pa.us>
To: "David G. Johnston" <david.g.johnston@gmail.com>
cc: Michael Lewis <mlewis@entrata.com>,
        Mithran Kulasekaran <mithranakulasekaran@gmail.com>,
        Pgsql Performance <pgsql-performance@lists.postgresql.org>
Subject: Re: Postgres views cannot use both union and join/where
In-reply-to: 
 <CAKFQuwZ+L+HLFs0Fdz2W4NYYxxk6Ko1CMQFyo-c1-ba-VuqDkA@mail.gmail.com>
References: 
 <CA+3AREwibh3aUugxAYmWOTja9bYSscC-Edk-7u=_VVYeAuirXQ@mail.gmail.com>
 <CAHOFxGppNR3m4Dc-1nL-YA-sqWjSWs7JEx7EgoPnPKk_-7c5KA@mail.gmail.com>
 <CAKFQuwZ+L+HLFs0Fdz2W4NYYxxk6Ko1CMQFyo-c1-ba-VuqDkA@mail.gmail.com>
Comments: In-reply-to "David G. Johnston" <david.g.johnston@gmail.com>
	message dated "Tue, 19 Oct 2021 20:38:40 -0700"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <2133338.1634738335.1@sss.pgh.pa.us>
Date: Wed, 20 Oct 2021 09:58:55 -0400
Message-ID: <2133339.1634738335@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/2133339.1634738335%40sss.pgh.pa.us>
Precedence: bulk

"David G. Johnston" <david.g.johnston@gmail.com> writes:
> On Tuesday, October 19, 2021, Michael Lewis <mlewis@entrata.com> wrote:
>> On Tue, Oct 19, 2021 at 3:48 PM Mithran Kulasekaran <
>> mithranakulasekaran@gmail.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