Thanks Tom and David That's very useful. My interest for Andl is to be able to emit SQL that Postgres will reliably interpret as an anti-join, in the absence of an explicit form in SQL. But your reference to "anti-semijoin" is interesting -- what is that? Is it just another name for anti-join, or something different? Does Postgres have one algorithm or two? [And BTW that is a weird piece of SQL -- I guess people really do write those things and you have to make the best of them you can.] Regards David M Bennett FACS Andl - A New Database Language - andl.org > -----Original Message----- > From: pgsql-general-owner@xxxxxxxxxxxxxx [mailto:pgsql-general- > owner@xxxxxxxxxxxxxx] On Behalf Of Tom Lane > Sent: Wednesday, 13 July 2016 12:13 AM > To: dandl <david@xxxxxxxx> > Cc: pgsql-general@xxxxxxxxxxxxxx > Subject: Re: Question about antijoin > > "dandl" <david@xxxxxxxx> writes: > > This got my interest! It's of great interest to me to know how and when > Postgres performs an anti-join (this being a significant omission from SQL). > > Is this a reliable trigger: (NOT EXISTS <subselect>)? > > That's one case; see convert_EXISTS_sublink_to_join() for the full set of > conditions involved. There is also a relevant transformation in > reduce_outer_joins(): > > * Another transformation we apply here is to recognize cases like > * SELECT ... FROM a LEFT JOIN b ON (a.x = b.y) WHERE b.y IS NULL; > * If the join clause is strict for b.y, then only null-extended rows could > * pass the upper WHERE, and we can conclude that what the query is really > * specifying is an anti-semijoin. We change the join type from JOIN_LEFT > * to JOIN_ANTI. The IS NULL clause then becomes redundant, and must be > * removed to prevent bogus selectivity calculations, but we leave it to > * distribute_qual_to_rels to get rid of such clauses. > > regards, tom lane > > > -- > Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make > changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
