Home > Differences, JOINS, SQL DB Engine > Physical Join vs Logical Join

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

Logical Joins:
Inner/Outer/Cross: http://www.mssqltips.com/tip.asp?tip=2115

Physical Joins:
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

About these ads
  1. October 6, 2010 at 2:15 pm

    Manoj,you always help me with this tough SQL structure … Thanks for posting this –Saurabh

  2. Bharath
    January 1, 2014 at 9:41 pm

    Hi the link u gave in the blog (ur blog) says page cannot be found

    • January 2, 2014 at 9:41 am

      Thanks @Bharath,
      I’ve corrected the broken link.

  1. November 19, 2012 at 12:37 am

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 411 other followers

%d bloggers like this: