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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
