On Wed, Oct 20, 2021 at 6:58 AM Tom Lane <t...@sss.pgh.pa.us> wrote: > "David G. Johnston" <david.g.johns...@gmail.com> writes: > > On Tuesday, October 19, 2021, Michael Lewis <mle...@entrata.com> wrote: > >> On Tue, Oct 19, 2021 at 3:48 PM Mithran Kulasekaran < > >> mithranakulaseka...@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. >
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.
