SQL Server Performance

JOIN's syntax difference

Discussion in 'SQL Server 2008 T-SQL Performance Tuning' started by mrozik, Oct 25, 2011.

  1. mrozik New Member

    I have a query with join:

    Code:
    FROM Firm f
    LEFT JOIN Emploee e ON e.Id = f.EmploeeId
    LEFT JOIN EmplState s ON e.StateId = s.Id
    today I found very interesting sample, and I transalated my query to syntax:

    Code:
    FROM Firm f
    LEFT JOIN Emploee e
        INNER JOIN EmplState s ON e.StateId = s.Id
    ON e.Id = f.EmploeeId
    - queries return the same resultset - could anybody point me to some resources about this syntax? what is difference? which syntax is more efficient?

    mrozik
  2. MichaelB Member

    inner joins are more efficient but it depends on the data and the table relationships. If you have a one to one relationship with no keys missing it the left join means nothing.
  3. Shehap MVP, MCTS, MCITP SQL Server

    As a base line of joins , they are classified into 2 types :

    · Logical joins (Inner join + left outer join + left right join + full outer join)

    · Physical joins (Hash joins +Merge Joins+ Nested loop)

    Where any type of logical joins above could select any type of physical joins to use while joining data entity either Tables , Views , CTEs ,TVFs…etc.

    So performance wise here depends basically on selection of physical join type which depends as well on:

    · Data entity size
    · Index design
    · Table hints used
    · Query hints used
  4. satya Moderator

Share This Page