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

Physical Join vs Logical Join in SQL Server

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.

Reference for Logical Joins from one of my old posts: https://sqlwithmanoj.wordpress.com/2009/03/12/sql-server-joins-and-types

–> Logical Joins:

– Inner/Outer/Cross: https://blogs.msdn.microsoft.com/craigfr/2006/07/19/introduction-to-joins/

–> Physical Joins:

– Nested Loop Joins: https://blogs.msdn.microsoft.com/craigfr/2006/07/26/nested-loops-join/

– Merge Joins: https://blogs.msdn.microsoft.com/craigfr/2006/08/03/merge-join/

– Hash Joins: https://blogs.msdn.microsoft.com/craigfr/2006/08/10/hash-join/

  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
  2. March 28, 2015 at 10:36 pm

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 )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: