title: SQL Sub-query or INNER-JOIN?
body: <p>I've the two following queries:</p>
  
  <pre><code>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
  </code></pre>
  
  <p><strong>Query execution plan statistics:</strong> (Query cost relative to batch)</p>
  
  <ul>
  <li>Query#1 (Sub-Query) : 56%</li>
  <li>Query#2   (JOIN) : 44%</li>
  </ul>
  
  <p>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.</p>
  
  <p>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)</p>
  
  <p>Thank you.</p>