shsteimer shsteimer - 11 months ago 74
SQL Question

subselect vs outer join

Consider the following 2 queries:

select tblA.a,tblA.b,tblA.c,tblA.d
from tblA
where tblA.a not in (select tblB.a from tblB)

select tblA.a,tblA.b,tblA.c,tblA.d
from tblA left outer join tblB
on tblA.a = tblB.a where tblB.a is null

Which will perform better? My assumption is that in general the join will be better except in cases where the subselect returns a very small result set.

Tom Tom
Answer Source

RDBMSs "rewrite" queries to optimize them, so it depends on system you're using, and I would guess they end up giving the same performance on most "good" databases.

I suggest picking the one that is clearer and easier to maintain, for my money, that's the first one. It's much easier to debug the subquery as it can be run independently to check for sanity.