SQL子查询还是INNER-JOIN？

Question

我有以下两个查询：

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

查询执行计划统计信息：（ 相对于批次的查询成本）

• 查询1（子查询）：56％
• 查询＃2（加入）：44％

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

请建议。另外，作为SQL Server中级用户，我如何得出哪个查询更好（如果有帮助的话，除执行计划外，其他都可以）

谢谢你。

Answer 1

连接比子查询快。

子查询导致磁盘访问繁忙，请考虑访问硬盘时来回 读写的针（头？）
：User，SearchExpression，PageSize，DrilldownPageSize，User，SearchExpression，PageSize，DrilldownPageSize，User
…等在。

通过加入作品 集中
在第一两个表的结果的操作，任何后续的联接将集中于内存中的接合（或缓存到磁盘）所述第一连接表的结果，等等。较少的读写针移动，因此速度更快