SQL 子查询还是 INNER-JOIN? [英] SQL Sub-query or INNER-JOIN?

问题描述

我有以下两个查询:

declare @UserId as int
set @UserId = 1

-- Query #1: Sub-query
SELECT
    u.[Id] ,
    u.[Name] ,
    u.[OrgId] AS Organization,
    (SELECT o.[Name] FROM Org o WHERE o.Id = u.OrgId) As OrganizationName,
    [UserRoleId] AS UserRole,
    [UserCode] AS UserCode,
    [EmailAddress] As EmailAddress, 
    (SELECT SearchExpression FROM SearchCriteria WHERE UserId = @UserId AND IsDefault=1 ) AS SearchCriteria,
    (SELECT PageSize FROM UserPreferences WHERE UserId = @UserId) AS UserPreferencePageSize,
    (SELECT DrilldownPageSize FROM UserPreferences WHERE UserId = @UserId) AS UserPreferenceDrilldownPageSize
    FROM [User] as u
WHERE u.Id = @UserId

-- Query #2: LEFT OUTER JOIN-query
SELECT
    u.[Id] ,
    u.[Name] ,
    u.[OrgId] AS Organization,
    (SELECT o.[Name] FROM Org o WHERE o.Id = u.OrgId) As OrganizationName,
    [UserRoleId] AS UserRole,
    [UserCode] AS UserCode,
    [EmailAddress] As EmailAddress, 
    sc.SearchExpression As SearchExpression,
    up.PageSize As PageSize,
    up.DrilldownPageSize As DrilldownPageSize    
    FROM [User] as u
LEFT OUTER JOIN [UserPreferences] as up ON u.id = up.UserId
LEFT OUTER JOIN [SearchCriteria] as sc ON u.id = sc.UserId
    WHERE ISNULL(sc.IsDefault,1)=1 AND u.Id = @UserId

查询执行计划统计:(相对于批处理的查询成本)

Query execution plan statistics: (Query cost relative to batch)

• 查询#1(子查询):56%
• 查询#2(连接):44%

我认为子查询将是最佳的，因为子查询将在应用 WHERE 过滤器后执行.统计数据表明 Query#2 - JOIN 方法更好.

I thot the sub-query would be optimal because the sub-query will be executed after the WHERE filter is applied. The statistics say the Query#2 - JOIN approach is better.

请建议.同样作为中等 SQL-Server 用户，我如何推导出哪个查询更好(执行计划之外的任何其他内容，如果它更有帮助)

Pls suggest. Also as a moderate SQL-Server user how can I derive which query is better (anything other then execution-plan, if it is more helpful)

谢谢.

推荐答案

join 比子查询更快.

join is faster than subquery.

子查询使磁盘访问繁忙，想想硬盘的读写针(头?)在访问时来回:User，SearchExpression，PageSize，DrilldownPageSize，User，SearchExpression，PageSize、DrilldownPageSize、User... 等等.

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.

join 的工作原理是集中对前两个表的结果进行操作，任何后续的连接都将集中在第一个连接表的内存中(或缓存到磁盘)结果上，并且很快.更少的读写针移动，从而更快

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

这篇关于SQL 子查询还是 INNER-JOIN?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！