"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. 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
