JOIN’s syntax difference | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

JOIN’s syntax difference

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
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.
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
Well in any case keeping in touch with Books Online can help you as first hand information about these joins: see http://msdn.microsoft.com/en-us/library/ms166018.aspx fyi
]]>

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |