sql server中的子查询v/s内部连接 [英] Subquery v/s inner join in sql server

问题描述

我有以下疑问

第一个使用内连接

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'

第二个使用子查询如

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'
)

在这个项目表中 [Pharmacy].[tblitemHdr] 包含 15 列和 2000 条记录.而 [Pharmacy].[tblitemHdr] 包含 5 列和大约 100 条记录.在这种情况下哪个查询给了我更好的性能?

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?

推荐答案

通常连接会比内部查询更快，但实际上它取决于 SQL Server 生成的执行计划.无论您如何编写查询，SQL Server 始终会根据执行计划对其进行转换.如果它足够聪明"，可以从两个查询生成相同的计划，您将得到相同的结果.

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.

这里 和 这里 一些帮助链接.

Here and here some links to help.

这篇关于sql server中的子查询v/s内部连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！