When working with SQL Server, it is important to understand the different types of JOINs and how they can affect performance. In this article, we will explore the concept of INNER JOIN and how it can be simulated using other JOIN types.

First, let’s clarify that INNER JOIN should be used where it is necessary, and simulating INNER JOIN using other JOIN types can degrade performance. However, if there are opportunities to convert any OUTER JOIN to INNER JOIN, it should be done with priority.

Let’s take a look at an example to illustrate this concept:

USE AdventureWorks;

-- Example of INNER JOIN
SELECT p.ProductID, piy.ProductID
FROM Production.Product p
INNER JOIN Production.ProductInventory piy
ON piy.ProductID = p.ProductID;

-- Example of LEFT JOIN simulating INNER JOIN
SELECT p.ProductID, piy.ProductID
FROM Production.Product p
LEFT JOIN Production.ProductInventory piy
ON 1 = 1
WHERE piy.ProductID = p.ProductID;

After running these two scripts, you will notice that the result sets are identical. This observation led me to wonder about the behind-the-scenes plan.

When examining the actual execution plan of the query, it becomes clear that even though a LEFT JOIN is used, the SQL Server Query Optimizer converts it to an INNER JOIN. The optimizer determines that there is no need for an OUTER LEFT JOIN and that an INNER JOIN will provide better performance.

This scenario highlights the intelligence of the Query Optimizer Engine and how it can save performance-related issues for sub-optimal queries.

Now, let’s delve into why a LEFT JOIN acts as an INNER JOIN in this case. When the condition “1 = 1” is used in the ON clause, it is always true and converts the LEFT JOIN to a CROSS JOIN. However, when the effect of the WHERE condition is applied to this CROSS JOIN, it produces a result similar to an INNER JOIN in our case. The SQL Server Query Optimizer recognizes this and uses an INNER JOIN instead.

This concept can be a good interview question to ask: “How do you write an OUTER JOIN that gives you the exact same result, execution plan, and performance as an INNER JOIN?”

If you have any other explanations or similar examples, please let me know, and I will post them on this blog.