SQL SERVER – Query Without Join Showing Query Plan With Join

Recently One of my clients asked me questions about why their query plan is displaying the join when the query has no join syntax and it is just on a single table. I often discuss this in my Comprehensive Database Performance Health Check. This question often keeps on coming in my consulting engagements. Let us quickly see the answer in the simplest possible way about Query Without Join Showing Query Plan With Join.

Two Types of Join

SQL Server has two different types of joins. The first one is Logical Joins and the second one is Physical Joins. I have previously written about this topic here: What is the Difference Between Physical and Logical Operation in SQL Server Execution Plan?

Logical Operators
Logical operators describe the relational operation used to process a statement. They describe conceptually what operation needs to be performed.
Examples: Right Anti Semi, Segment Repartition

Physical Operators
Physical operators implement the operation described by logical operators. Each physical operator is an object or routine that performs an operation. The physical operators initialize, collect data, and close.
Examples: Index Scan, Clustered Index Delete

Simple Query

Let us run the following query for a sample database WideWorldImporters. Make sure that you have enabled the execution plan.

SELECT *
FROM WideWorldImporters.Sales.OrderLines
WHERE OrderID = 3982

Here is the execution plan for the query.

You will notice in the query execution plan that even though there is no join in the query, the execution plan has join operator.

This is because of how the query is retrieving the data. The join is happening to efficiently retrieve data. The query is using an index to retrieve data for a couple of columns.

The rest of the data in which query need is retrieved from the clustered index. As the data is coming from two different sources, they need to be combined together and that is why the join is required.

Now a common follow up question I often receive is that why I SQL Server does not use clustered index directly instead of doing the join. The answer in one word is Efficiency. We will understand this in detail in the future.

Reference: Pinal Dave (https://blog.sqlauthority.com)