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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On Wed, Oct 20, 2021 at 6:58 AM Tom Lane <tgl@xxxxxxxxxxxxx> wrote:
"David G. Johnston" <david.g.johnston@xxxxxxxxx> writes:
> On Tuesday, October 19, 2021, Michael Lewis <mlewis@xxxxxxxxxxx> wrote:
>> On Tue, Oct 19, 2021 at 3:48 PM Mithran Kulasekaran <
>> mithranakulasekaran@xxxxxxxxx> 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.


[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux