Re: Anti join confusion

On Tue, Feb 25, 2025 at 1:30 AM Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> On Mon, Feb 24, 2025 at 8:08 AM wenhui qiu <qiuwenhuifx(at)gmail(dot)com> wrote:
> > Actually ,Many fork postgresql databases have already implemented ,For example, if the relevant field has a non-null constraint ，Many databases can do the same thing as not exist （ MySQL ，SQL Server，Oracle）

> I'm not surprised to hear it. Long-time PostgreSQL users just don't
> use NOT IN, so it's fine, but anyone coming from another database gets
> hosed. I think it would be good to put some effort into improving this
> area, but I do not have time to work on it myself.

I agree that it'd be beneficial to make some improvements to NOT IN
subqueries. From what I can see, we may have two potential options:

* As Tom mentioned, we can prove that the subquery's output never
contains NULL values and then convert the NOT IN into an anti-join.
(It seems to me that we would also need to prove that the outer side
never contains NULL values either, because whether the NULL values
from the outer side should be included in the output depends on
whether the inner side is empty.)

* We can add support in the executor to handle the NULL semantics of
NOT IN. This may require inventing a new join type.

I'm not quite sure which option is more promising at the moment, or if
there are other options to consider.

Thanks
Richard