哪个查询性能更高? [英] Which query is more performant?

问题描述

假设我有一个自我联接查询，其内部联接的结果与外部联接的结果完全相同.在这种情况下，性能要比另一种高吗?它是否因查询优化器的功能而有所不同?

Suppose I have a self join query whose results are precisely the same for an inner join as they are for an outer join. In that case, is one more performant than another, or does it vary by what the query optimizer does?

通常，我希望LOJ的性能较差，因为它必须执行INNER JOIN的所有工作以及对结果进行null扩展的额外工作.但是，如果不需要额外的工作，理论上的性能是否应该相同?我自己尝试过此操作，但没有注意到任何一致的性能差异，但想在此处进行确认.

Typically I'd expect a LOJ to be less performant as it has to do all the work of an INNER JOIN plus the extra work of null-extending the results. But if that extra work isn't needed, should performance in theory be the same? I've tried this myself, but haven't noticed any consistent perf differences, but wanted to ask here to be sure.

推荐答案

回答问题的正确方法是对系统上的数据运行查询.

The correct way to answer your question is by running the queries on your data on your system.

通常，外部联接的作用仅仅是减少优化器可以做出的选择.如果是这种情况，则可以使用基本上相同的查询计划-并且查询是相同的

Often, the effect of the outer join is simply to reduce the choices the optimizer can make. If this is the case, then essentially the same query plan might be used -- and the queries are identical

当然，效果可能更加戏剧化.使用外部联接时，最终的查询计划可能会更糟.

Of course, the effect could be much more dramatic. The resulting query plan could be much worse with the outer join.

这篇关于哪个查询性能更高?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！