Physical Join vs Logical Join
Most of us know about JOINS in SQL Server and their types. But do we really know how they are interpreted in SQL Server internally.
Today I found lot of informative, interesting and important sources regarding Logical and Physical joins in SQL Server (links below).
Classifying JOINS mainly into 2 types:
1. Logical Joins: These joins are simple joins that we apply in our SQL queries, like INNER JOIN, RIGHT/LEFT OUTER JOIN, CROSS JOIN, OUTER APPLY, etc.
2. Physical Joins: These are the joins that users don’t use/write in their SQL queries. Instead these are implemented inside SQL Server engine as operators or algorithms to implement the Logical Joins. Their types are Nested Loop, Merge and Hash.
For a particular SQL query when you try to view an Estimated Execution Plan or execute a query by selecting Actual Execution Plan, you can clearly see these Physical Joins under the Execution Plan tab in SSMS.
Would include more information in my next posts about Physical Joins.
Reference for Logical Joins from one of my old posts: http://sqlwithmanoj.wordpress.com/2009/03/12/sql-server-joins-and-types
More Info below:
Source MSDN link: http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/e50d0a33-f319-42ce-8649-4b9697d298f7
Nested Loop Joins: http://blogs.msdn.com/b/craigfr/archive/2006/07/19/671712.aspx
Merge Joins: http://blogs.msdn.com/b/craigfr/archive/2006/08/03/merge-join.aspx
Hash Joins: http://blogs.msdn.com/b/craigfr/archive/2006/08/10/687630.aspx