blocks|key|1620500|text|Outer+join通常会给你更多的结果(A*B而不是WHERE+A=B)。那会花更多的时间。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1620501|entityMap^0|L|3|R|9|0^^$0|@$1|2|3|4|5|6|7|H|8|@$9|I|A|J|B|C]|$9|K|A|L|B|C]]|D|@]|E|$]]|$1|F|3|-4|5|6|7|M|8|@]|D|@]|E|$]]]|G|$]]

Outer join will normally give you MANY more results (<code>A*B</code> instead of <code>WHERE A=B</code>). That'll take more time.

blocks|key|877599|text|通常，这是因为db引擎必须执行更多的比较操作来缩小结果集的范围。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|877600|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

In general, it is because the db engine has to perform much more comparison operations to narrow down the result set.

blocks|key|875541|text|inner+join会消除行，而outer+join不会，这会导致更多的行需要处理。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|875542|看一下可视化地描述连接的this+article。请注意，与左侧和完整的outer+join相比，inner+join的结果集要小得多。这些图片并不代表每个场景，但它们可以让您对正在发生的事情有所了解。|875543|entityMap|0|LINK|mutability|MUTABLE|url|http://www.codinghorror.com/blog/2007/10/a-visual-explanation-of-sql-joins.html^0|0|A|G|A|0|10|A|1D|A|C|C|0|0^^$0|@$1|2|3|4|5|6|7|P|8|@$9|Q|A|R|B|C]|$9|S|A|T|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|U|8|@$9|V|A|W|B|C]|$9|X|A|Y|B|C]]|D|@$9|Z|A|10|1|11]]|E|$]]|$1|H|3|-4|5|6|7|12|8|@]|D|@]|E|$]]]|I|$J|$5|K|L|M|E|$N|O]]]]

An <code>inner join</code> will eliminate rows while an <code>outer join</code> doesn't. This results in a lot more rows to process.

Take a look at <a href="http://www.codinghorror.com/blog/2007/10/a-visual-explanation-of-sql-joins.html" rel="noreferrer">this article</a> that visually describes joins. Notice how much smaller the result set is for the <code>inner join</code> vs the left and full <code>outer join</code>. These pictures don't represent every scenario but they give you an idea of what is going on.

blocks|key|1620558|text|用真实数据进行测试。可能的情况是，除非外连接引入额外的行，否则性能是相同的。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1620559|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

Test it with real data.
It may be that unless the outer join introduces additional rows, the performance is the same.

blocks|key|875581|text|我在我的网站上遇到了性能问题，发现一个查询需要7秒才能完成。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|875582|此查询中的所有列都是数字并已编制索引(SQL+Server2008+R2)|875583|这个完整的外连接是在3个表之间，每个表有(150,350和270)千行。|875584|我简单地将完整的外部连接替换为左连接，执行时间减少到0秒(毫秒)。|875585|在我用几行代码进行测试之前，如果表有数千或数百万条记录，那么完整的外连接的性能将不会很好。|875586|entityMap^0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|L|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|M|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|N|8|@]|9|@]|A|$]]|$1|F|3|G|5|6|7|O|8|@]|9|@]|A|$]]|$1|H|3|I|5|6|7|P|8|@]|9|@]|A|$]]|$1|J|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|K|$]]

I had a problem with performance im my website and found a query taking 7 seconds to complete

All the columns in this query were numbers and were indexed(SQL Server 2008 R2)

This full outer join were between 3 tables with (150, 350 and 270) thousand lines each.

I simply replaced the FULL OUTER JOIN for LEFT JOIN and execution time reduced to 0 seconds(miliseconds)

Before i just tested with few lines
Then, if tables have thousands or millions of records, the performance of a full outer join will not be good.

blocks|key|1620601|text|有些时候，被忽略的连接比内部连接执行得更快。这将取决于假设连接的2个结果集|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1620602|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

Some times left out join perform faster than inner joins. this will depends on the 2 results sets which supposed to be joined

Why the outer joins are in general slower than inner joins? I mean, independently from the database vendor. I suppose it's a matter of implementation or the access plan used, but I wasn't able to convince a colleague of mine who thinks performance should be the same. 

Thanks in advance
Lluis

Outer join performance

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

为什么外连接通常比内连接慢？我的意思是，独立于数据库供应商。我认为这是一个实现或使用的访问计划的问题，但我无法说服我的同事，他认为性能应该是相同的。提前谢谢你，Lluis

问外部连接性能
EN

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问外部连接性能EN