SQL Sub-query or INNER-JOIN?

Question

I've the two following queries:

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)

• Query#1 (Sub-Query) : 56%
• Query#2 (JOIN) : 44%

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.

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)

Thank you.

Answer 1

join is faster than subquery.

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