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
    mrozik, Oct 25, 2011
    #1

  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.
    MichaelB, Oct 27, 2011
    #2

  3. Shehap Moderator

    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
    Shehap, Oct 29, 2011
    #3

  4. satya Moderator

    satya, Nov 22, 2011
    #4

Share This Page