shsteimer shsteimer - 1 month ago 8
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

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.