"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
