From: Tom Lane <tgl@sss.pgh.pa.us>
To: David G. Johnston <david.g.johnston@gmail.com>
Cc: Michael Lewis <mlewis@entrata.com>
Cc: Mithran Kulasekaran <mithranakulasekaran@gmail.com>
Cc: Pgsql Performance <pgsql-performance@lists.postgresql.org>
Subject: Re: Postgres views cannot use both union and join/where
Date: Wed, 20 Oct 2021 09:58:55 -0400
Message-ID: <2133339.1634738335@sss.pgh.pa.us> (raw)
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>
"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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, david.g.johnston@gmail.com, mlewis@entrata.com, mithranakulasekaran@gmail.com, pgsql-performance@lists.postgresql.org
Subject: Re: Postgres views cannot use both union and join/where
In-Reply-To: <2133339.1634738335@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox