Re: nested query vs left join: query planner very confused

On 11/27/2013 04:56 PM, David Rysdam wrote:
> I've got two tables, sigs and mags. It's a one-to-one relationship, mags
> is just split out because we store a big, less-often-used field
> there. "signum" is the key field.
>
> Sometimes I want to know if I have any orphans in mags, so I do a query
> like this:
>
>     select signum from lp.Mags where signum is not null and signum not
>     in (select lp.Sigs.signum from lp.Sigs)

[...]

> I also decided to try doing the query a different way:
>
>      select lp.mags.signum from lp.mags left join lp.sigs on
>      lp.mags.signum = lp.sigs.signum where lp.mags.signum is not null
>      and lp.sigs.signum is null;
>
> This one runs fast for both of us. So I guess my second question is: why
> can't the query planner tell these are the same query?

Because they're not the same query.  NOT IN has a bunch of semantics
issues regarding nulls which the anti-join in the second query does not
have.

--
Vik