From: | "David G(dot) Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | Michael Lewis <mlewis(at)entrata(dot)com>, Mithran Kulasekaran <mithranakulasekaran(at)gmail(dot)com>, Pgsql Performance <pgsql-performance(at)lists(dot)postgresql(dot)org> |
Subject: | Re: Postgres views cannot use both union and join/where |
Date: | 2021-10-20 14:29:51 |
Message-ID: | CAKFQuwbGL65YosG0wgXCDcijJv8JYjLHdo8fyukS2GFi9uXf1g@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
On Wed, Oct 20, 2021 at 6:58 AM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> "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.
>
Yeah, my bad. I was actually thinking this but then figured the OP
wouldn't have written an anti-join that didn't actually work.
My original email was going to be:
Adding the single table expression to the ON clause is shorthand for
writing:
SELECT t.* FROM template AS t LEFT JOIN (SELECT * FROM template_staging
WHERE template_staging.name IS NULL) AS ts ON t.name = ts.name;
David J.
From | Date | Subject | |
---|---|---|---|
Next Message | Ninad Shah | 2021-10-22 14:41:17 | Re: Query out of memory |
Previous Message | Tom Lane | 2021-10-20 13:58:55 | Re: Postgres views cannot use both union and join/where |