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

["David G. Johnston" <david.g.johnston@xxxxxxxxx>]

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.

It could also be written as an anti-join:

Select * from template as t where not exists (select 1 from template_staging as ts where t.name = ts.name)

David J.