subselect vs outer join [英] subselect vs outer join

问题描述

请考虑以下2个查询：

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

$ b b

哪种效果更好？我的假设是，除非在子查询返回非常小的结果集的情况下，连接通常会更好。

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.

推荐答案

RDBMS 重写查询以优化它们，因此它取决于您使用的系统，我猜想他们最终在大多数好数据库上提供相同的性能。

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.

这篇关于subselect vs outer join的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！