sql server中的子查询v/s内部连接
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14052596/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Subquery v/s inner join in sql server
提问by Nithesh Narayanan
I have following queries
我有以下疑问
First one using inner join
第一个使用内连接
SELECT item_ID,item_Code,item_Name
FROM [Pharmacy].[tblitemHdr] I
INNER JOIN EMR.tblFavourites F ON I.item_ID=F.itemID
WHERE F.doctorID = @doctorId AND F.favType = 'I'
second one using sub query like
第二个使用子查询，如
SELECT item_ID,item_Code,item_Name from [Pharmacy].[tblitemHdr]
WHERE item_ID IN
(SELECT itemID FROM EMR.tblFavourites
WHERE doctorID = @doctorId AND favType = 'I'
)
In this item table [Pharmacy].[tblitemHdr]Contains 15 columns and 2000 records. And [Pharmacy].[tblitemHdr]contains 5 columns and around 100 records. in this scenario which query gives me better performance?
在这个项目表中[Pharmacy].[tblitemHdr]包含 15 列和 2000 条记录。并[Pharmacy].[tblitemHdr]包含 5 列和大约 100 条记录。在这种情况下which query gives me better performance?
采纳答案by Diego
Usually joins will work faster than inner queries, but in reality it will depend on the execution plan generated by SQL Server. No matter how you write your query, SQL Server will always transform it on an execution plan. If it is "smart" enough to generate the same plan from both queries, you will get the same result.
通常连接会比内部查询更快，但实际上它取决于 SQL Server 生成的执行计划。无论您如何编写查询，SQL Server 始终会根据执行计划对其进行转换。如果它足够“智能”以从两个查询生成相同的计划，您将得到相同的结果。
回答by Arno 2501
In Sql Server Management Studio you can enable "Client Statistics" and also Include Actual Execution Plan. This will give you the ability to know precisely the execution time and load of each request.
在 Sql Server Management Studio 中，您可以启用“客户端统计信息”并包括实际执行计划。这将使您能够准确了解每个请求的执行时间和负载。
Also between each request clean the cache to avoid cache side effect on performance
同样在每个请求之间清理缓存以避免缓存对性能的副作用
USE <YOURDATABASENAME>;
GO
CHECKPOINT;
GO
DBCC DROPCLEANBUFFERS;
GO
I think it's always best to see with our own eyes than relying on theory !
我认为亲眼所见总是比依靠理论更好！
回答by Paz
join is faster than subquery.
join 比子查询快。
subquery makes for busy disk access, think of hard disk's read-write needle(head?) that goes back and forth when it access: User, SearchExpression, PageSize, DrilldownPageSize, User, SearchExpression, PageSize, DrilldownPageSize, User... and so on.
子查询使磁盘访问繁忙，想想访问时来回的硬盘读写针（头？）：User，SearchExpression，PageSize，DrilldownPageSize，User，SearchExpression，PageSize，DrilldownPageSize，User...等等在。
join works by concentrating the operation on the result of the first two tables, any subsequent joins would concentrate joining on the in-memory(or cached to disk) result of the first joined tables, and so on. less read-write needle movement, thus faster
联接的工作原理是将操作集中在前两个表的结果上，任何后续联接都将集中在第一个联接表的内存中（或缓存到磁盘）结果上，依此类推。更少的读写针移动，因此更快
Source: Here
来源：这里
回答by Paz
Sub-query Vs Join
子查询与加入
Table one 20 rows,2 cols
表一 20 行，2 列
Table two 20 rows,2 cols
表二 20 行，2 列
sub-query 20*20
子查询20*20
join 20*2
加入20*2
logical, rectify
合乎逻辑，纠正
Detailed
详细的
The scan count indicates multiplication effect as the system will have to go through again and again to fetch data, for your performance measure, just look at the time
扫描计数表示倍增效应，因为系统将不得不一遍又一遍地获取数据，对于您的性能衡量，只需查看时间
回答by murali krishna
First query is better than second query.. because first query we are joining both table. and also check the explain plan for both queries...
第一个查询比第二个查询好..因为第一个查询我们加入了两个表。并检查两个查询的解释计划......
回答by ammad khan
It all depends on data and relational mapping between tables. If RDBMS rules are not followed then even the first query will be slow on execution and data fetching.
这一切都取决于数据和表之间的关系映射。如果不遵循 RDBMS 规则，那么即使是第一个查询在执行和数据获取上也会很慢。
