blocks|key|3663734|text|奇怪的是:对我来说，更自然的做法是先把它们写成一个相关的查询，在这一点上，我必须返回并重构才能使用连接，因为根据我的经验，sql+server优化器更有可能做到这一点。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3663735|但别把我当回事。尽管我在这里有26K的代表，并且是当前仅有的两个特定于sql主题的徽章之一，但实际上我在sql知识方面相当初级(都是关于卷！;+)；当然，我不是DBA。在实践中，您当然需要分析每个方法以衡量其实际性能。我希望优化器能够识别出您所请求的内容，并以最佳方式处理这两个查询，但只有在检查之后，您才会知道。|3663736|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|F|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|G|8|@]|9|@]|A|$]]|$1|D|3|-4|5|6|7|H|8|@]|9|@]|A|$]]]|E|$]]

Odd: it's normally more natural for me to write these as a correlated query first, at which point I have to then go back and re-factor to use a join because in my experience the sql server optimizer is more likely to get that right.

But don't take me too seriously. For all I have 26K rep here and one of only 2 current sql topic-specific badges, I'm actually pretty junior in terms of sql knowledge (It's all about the volume! ;) ); certainly I'm no DBA. In practice, you will of course need to profile each method to gauge it's actual performance. I would expect the optimizer to recognize what you're asking for and handle either query in the optimal way, but you never know until you check.

blocks|key|1088541|text|我刚刚运行了一个测试查询，这两个语句以完全相同的执行计划结束。当然，对于任何性能问题，我建议在您自己的环境中运行测试；使用SQL+server+Management+Studio很容易(如果您运行的是2000，则可以使用SQL+Query+Analyzer+)。只需在查询窗口中键入这两个语句，选择Query%7CInclude+Actual+query+Plan。然后运行查询。转到results选项卡，您可以很容易地看到计划是什么，以及哪个计划的成本更高。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1088542|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|$]]

I just ran a test query and the two statements ended up with the exact same execution plan. Of course, for just about any performance question I would recommend running the test on your own environment; With SQL server Management Studio this is easy (or SQL Query Analyzer if your running 2000). Just type both statements into a query window, select Query|Include Actual Query Plan. Then run the query. Go to the results tab and you can easily see what the plans are and which one had a higher cost.

blocks|key|1088581|text|正如每个人都注意到的，这一切都归结于优化器。我建议以您觉得更自然的方式编写它，然后确保优化器可以找出最有效的查询计划(收集统计数据、创建索引等)。SQL+Server优化器总体上是相当不错的，只要您为它提供了需要使用的信息。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1088582|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|$]]

As everyone notes, it all boils down to the optimizer. I'd suggest writing it in whatever way feels more natural to you, then making sure the optimizer can figure out the most effective query plan (gather statistics, create an index, whatever). The SQL Server optimizer is pretty good overall, so long as you give it the information it needs to work with.

blocks|key|3663881|text|可能最好的性能是连接到派生表。Exists可能是下一个(而且可能更快)。最差的性能是在select中使用子查询，因为它倾向于逐行运行，而不是作为一个集合运行。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3663882|然而，在所有条件都相同的情况下，数据库性能非常依赖于数据库设计。我会尝试所有可能的方法，看看哪种方法在你的情况下更快。|3663883|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|F|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|G|8|@]|9|@]|A|$]]|$1|D|3|-4|5|6|7|H|8|@]|9|@]|A|$]]]|E|$]]

Probably the best performance is with a join to a derived table. Exists would probably be next (and might be faster). The worst performance would be with a subquery inside the select as it would tend to run row by row instead of as a set.

However, all things being equal and database performance being very dependent on the database design. I would try out all possible methods and see which are faster in your circumstances.

blocks|key|444396|text|使用联接。如果表很小，这可能不会对性能产生太大影响，但是如果“外部”表非常大，那么它将需要对每一行执行eXistes子查询。如果您的表是在公共列上建立索引的，那么进行内连接应该要快得多。顺便说一句，如果您想要查找第二个表中没有的所有行，请使用左连接并在第二个表中测试NULL+--当您有非常大的表和索引时，它比使用EXISTS快得多。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|444397|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|$]]

Use the join. It might not make much of a difference in performance if you have small tables, but if the "outer" table is very large then it will need to do the EXISTS sub-query for each row. If your tables are indexed on the common columns then it should be far quicker to do the INNER JOIN. BTW, if you want to find all rows that are NOT in the second table, use a LEFT JOIN and test for NULL in the second table--it is much faster than using EXISTS when you have very large tables and indexes.

let's say that you want to select all rows from one table that have a corresponding row in another one (the data in the other table is not important, only the presence of a corresponding row is important). From what I know about DB2, this kinda query is better performing when written as a correlated query with a EXISTS clause rather than a INNER JOIN. Is that the same for SQL Server? Or doesn't it make any difference whatsoever?

Correlated query vs inner join performance in SQL Server

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

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

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

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

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

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

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

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

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

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

假设您想从一个表中选择在另一个表中有对应行的所有行(另一个表中的数据并不重要，重要的是存在对应的行)。根据我对DB2的了解，当使用EXISTS子句编写相关查询而不是内部连接时，这种查询的性能会更好。SQL Server也是如此吗？或者这不会有任何不同？

问SQL Server中关联查询与内连接性能的比较
EN

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问SQL Server中关联查询与内连接性能的比较EN