SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
SET SHOWPLAN_XML ON;
-- Join eliminated
SELECT SOD.ProductID 
FROM Sales.SalesOrderDetail AS SOD
LEFT JOIN Production.Product AS P
    ON P.ProductID = SOD.ProductID;


Microsoft SQL Server 2005 XML Showplan


<ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.539" Build="15.0.4223.1"><BatchSequence><Batch><Statements><StmtSimple StatementText="-- Join eliminated&#xa;SELECT SOD.ProductID &#xa;FROM Sales.SalesOrderDetail AS SOD&#xa;LEFT JOIN Production.Product AS P&#xa;    ON P.ProductID = SOD.ProductID" StatementId="1" StatementCompId="1" StatementType="SELECT" RetrievedFromCache="false" StatementSubTreeCost="0.336731" StatementEstRows="121317" SecurityPolicyApplied="false" StatementOptmLevel="TRIVIAL" QueryHash="0x8CB2A7F1819B18CE" QueryPlanHash="0x8CD1C2E322C9B309" CardinalityEstimationModelVersion="150"><StatementSetOptions QUOTED_IDENTIFIER="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" NUMERIC_ROUNDABORT="false"></StatementSetOptions><QueryPlan NonParallelPlanReason="NoParallelPlansInDesktopOrExpressEdition" CachedPlanSize="16" CompileTime="15" CompileCPU="15" CompileMemory="208"><MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0" GrantedMemory="0" MaxUsedMemory="0"></MemoryGrantInfo><OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="1258238" EstimatedPagesCached="78639" EstimatedAvailableDegreeOfParallelism="1" MaxCompileMemory="1285568"></OptimizerHardwareDependentProperties><TraceFlags IsCompileTime="1"><TraceFlag Value="460" Scope="Global"></TraceFlag><TraceFlag Value="8017" Scope="Global"></TraceFlag></TraceFlags><RelOp NodeId="0" PhysicalOp="Index Scan" LogicalOp="Index Scan" EstimateRows="121317" EstimatedRowsRead="121317" EstimateIO="0.203125" EstimateCPU="0.133606" AvgRowSize="11" EstimatedTotalSubtreeCost="0.336731" TableCardinality="121317" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_671bd5fd15fe479ba94b6d828d3b2594]" Schema="[Sales]" Table="[SalesOrderDetail]" Alias="[SOD]" Column="ProductID"></ColumnReference></OutputList><IndexScan Ordered="0" ForcedIndex="0" ForceSeek="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Database="[fiddle_671bd5fd15fe479ba94b6d828d3b2594]" Schema="[Sales]" Table="[SalesOrderDetail]" Alias="[SOD]" Column="ProductID"></ColumnReference></DefinedValue></DefinedValues><Object Database="[fiddle_671bd5fd15fe479ba94b6d828d3b2594]" Schema="[Sales]" Table="[SalesOrderDetail]" Index="[IX_SalesOrderDetail_ProductID]" Alias="[SOD]" IndexKind="NonClustered" Storage="RowStore"></Object></IndexScan></RelOp></QueryPlan></StmtSimple></Statements></Batch></BatchSequence></ShowPlanXML>


-- Join not eliminated projecting from the preserved side of the join
SELECT P.ProductID 
FROM Sales.SalesOrderDetail AS SOD
LEFT JOIN Production.Product AS P
    ON P.ProductID = SOD.ProductID;


Microsoft SQL Server 2005 XML Showplan


<ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.539" Build="15.0.4223.1"><BatchSequence><Batch><Statements><StmtSimple StatementText="-- Join not eliminated projecting from the preserved side of the join&#xa;SELECT P.ProductID &#xa;FROM Sales.SalesOrderDetail AS SOD&#xa;LEFT JOIN Production.Product AS P&#xa;    ON P.ProductID = SOD.ProductID" StatementId="1" StatementCompId="1" StatementType="SELECT" RetrievedFromCache="false" StatementSubTreeCost="0.623015" StatementEstRows="121317" SecurityPolicyApplied="false" StatementOptmLevel="FULL" QueryHash="0xE00D29840937B23D" QueryPlanHash="0xAEAB1663B595F96E" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" CardinalityEstimationModelVersion="150"><StatementSetOptions QUOTED_IDENTIFIER="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" NUMERIC_ROUNDABORT="false"></StatementSetOptions><QueryPlan NonParallelPlanReason="NoParallelPlansInDesktopOrExpressEdition" CachedPlanSize="24" CompileTime="15" CompileCPU="15" CompileMemory="320"><MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0" GrantedMemory="0" MaxUsedMemory="0"></MemoryGrantInfo><OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="1258238" EstimatedPagesCached="78639" EstimatedAvailableDegreeOfParallelism="1" MaxCompileMemory="1285568"></OptimizerHardwareDependentProperties><OptimizerStatsUsage><StatisticsInfo LastUpdate="2022-08-06T17:26:36.89" ModificationCount="0" SamplingPercent="100" Statistics="[IX_SalesOrderDetail_ProductID]" Table="[SalesOrderDetail]" Schema="[Sales]" Database="[fiddle_671bd5fd15fe479ba94b6d828d3b2594]"></StatisticsInfo><StatisticsInfo LastUpdate="2022-08-06T17:26:34.56" ModificationCount="0" SamplingPercent="100" Statistics="[PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID]" Table="[SalesOrderDetail]" Schema="[Sales]" Database="[fiddle_671bd5fd15fe479ba94b6d828d3b2594]"></StatisticsInfo><StatisticsInfo LastUpdate="2022-08-06T17:26:34.28" ModificationCount="0" SamplingPercent="100" Statistics="[PK_Product_ProductID]" Table="[Product]" Schema="[Production]" Database="[fiddle_671bd5fd15fe479ba94b6d828d3b2594]"></StatisticsInfo><StatisticsInfo LastUpdate="2022-08-06T17:26:36.73" ModificationCount="0" SamplingPercent="100" Statistics="[AK_SalesOrderDetail_rowguid]" Table="[SalesOrderDetail]" Schema="[Sales]" Database="[fiddle_671bd5fd15fe479ba94b6d828d3b2594]"></StatisticsInfo></OptimizerStatsUsage><TraceFlags IsCompileTime="1"><TraceFlag Value="460" Scope="Global"></TraceFlag><TraceFlag Value="8017" Scope="Global"></TraceFlag></TraceFlags><RelOp NodeId="0" PhysicalOp="Merge Join" LogicalOp="Right Outer Join" EstimateRows="121317" EstimateIO="0" EstimateCPU="0.273556" AvgRowSize="11" EstimatedTotalSubtreeCost="0.623015" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_671bd5fd15fe479ba94b6d828d3b2594]" Schema="[Production]" Table="[Product]" Alias="[P]" Column="ProductID"></ColumnReference></OutputList><Merge ManyToMany="0"><InnerSideJoinColumns><ColumnReference Database="[fiddle_671bd5fd15fe479ba94b6d828d3b2594]" Schema="[Sales]" Table="[SalesOrderDetail]" Alias="[SOD]" Column="ProductID"></ColumnReference></InnerSideJoinColumns><OuterSideJoinColumns><ColumnReference Database="[fiddle_671bd5fd15fe479ba94b6d828d3b2594]" Schema="[Production]" Table="[Product]" Alias="[P]" Column="ProductID"></ColumnReference></OuterSideJoinColumns><Residual><ScalarOperator ScalarString="[fiddle_671bd5fd15fe479ba94b6d828d3b2594].[Production].[Product].[ProductID] as [P].[ProductID]=[fiddle_671bd5fd15fe479ba94b6d828d3b2594].[Sales].[SalesOrderDetail].[ProductID] as [SOD].[ProductID]"><Compare CompareOp="EQ"><ScalarOperator><Identifier><ColumnReference Database="[fiddle_671bd5fd15fe479ba94b6d828d3b2594]" Schema="[Production]" Table="[Product]" Alias="[P]" Column="ProductID"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Identifier><ColumnReference Database="[fiddle_671bd5fd15fe479ba94b6d828d3b2594]" Schema="[Sales]" Table="[SalesOrderDetail]" Alias="[SOD]" Column="ProductID"></ColumnReference></Identifier></ScalarOperator></Compare></ScalarOperator></Residual><RelOp NodeId="1" PhysicalOp="Clustered Index Scan" LogicalOp="Clustered Index Scan" EstimateRows="504" EstimatedRowsRead="504" EstimateIO="0.0120139" EstimateCPU="0.0007114" AvgRowSize="11" EstimatedTotalSubtreeCost="0.0127253" TableCardinality="504" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_671bd5fd15fe479ba94b6d828d3b2594]" Schema="[Production]" Table="[Product]" Alias="[P]" Column="ProductID"></ColumnReference></OutputList><IndexScan Ordered="1" ScanDirection="FORWARD" ForcedIndex="0" ForceSeek="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Database="[fiddle_671bd5fd15fe479ba94b6d828d3b2594]" Schema="[Production]" Table="[Product]" Alias="[P]" Column="ProductID"></ColumnReference></DefinedValue></DefinedValues><Object Database="[fiddle_671bd5fd15fe479ba94b6d828d3b2594]" Schema="[Production]" Table="[Product]" Index="[PK_Product_ProductID]" Alias="[P]" IndexKind="Clustered" Storage="RowStore"></Object></IndexScan></RelOp><RelOp NodeId="2" PhysicalOp="Index Scan" LogicalOp="Index Scan" EstimateRows="121317" EstimatedRowsRead="121317" EstimateIO="0.203125" EstimateCPU="0.133606" AvgRowSize="11" EstimatedTotalSubtreeCost="0.336731" TableCardinality="121317" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_671bd5fd15fe479ba94b6d828d3b2594]" Schema="[Sales]" Table="[SalesOrderDetail]" Alias="[SOD]" Column="ProductID"></ColumnReference></OutputList><IndexScan Ordered="1" ScanDirection="FORWARD" ForcedIndex="0" ForceSeek="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Database="[fiddle_671bd5fd15fe479ba94b6d828d3b2594]" Schema="[Sales]" Table="[SalesOrderDetail]" Alias="[SOD]" Column="ProductID"></ColumnReference></DefinedValue></DefinedValues><Object Database="[fiddle_671bd5fd15fe479ba94b6d828d3b2594]" Schema="[Sales]" Table="[SalesOrderDetail]" Index="[IX_SalesOrderDetail_ProductID]" Alias="[SOD]" IndexKind="NonClustered" Storage="RowStore"></Object></IndexScan></RelOp></Merge></RelOp></QueryPlan></StmtSimple></Statements></Batch></BatchSequence></ShowPlanXML>


-- "Fun" rewrite
SELECT p.[Name]
FROM Production.Product AS P
RIGHT JOIN Sales.SalesOrderDetail AS SOD
JOIN Sales.SalesOrderHeader AS SOH
    ON SOH.SalesOrderID = SOD.SalesOrderID
    ON SOD.ProductID = P.ProductID;


Microsoft SQL Server 2005 XML Showplan


<ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.539" Build="15.0.4223.1"><BatchSequence><Batch><Statements><StmtSimple StatementText="-- &quot;Fun&quot; rewrite&#xa;SELECT p.[Name]&#xa;FROM Production.Product AS P&#xa;RIGHT JOIN Sales.SalesOrderDetail AS SOD&#xa;JOIN Sales.SalesOrderHeader AS SOH&#xa;    ON SOH.SalesOrderID = SOD.SalesOrderID&#xa;    ON SOD.ProductID = P.ProductID" StatementId="1" StatementCompId="1" StatementType="SELECT" RetrievedFromCache="false" StatementSubTreeCost="0.623015" StatementEstRows="121317" SecurityPolicyApplied="false" StatementOptmLevel="FULL" QueryHash="0x98FD93F94B76F481" QueryPlanHash="0xAEAB1663B595F96E" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" CardinalityEstimationModelVersion="150"><StatementSetOptions QUOTED_IDENTIFIER="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" NUMERIC_ROUNDABORT="false"></StatementSetOptions><QueryPlan NonParallelPlanReason="NoParallelPlansInDesktopOrExpressEdition" CachedPlanSize="24" CompileTime="15" CompileCPU="15" CompileMemory="504"><MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0" GrantedMemory="0" MaxUsedMemory="0"></MemoryGrantInfo><OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="1258238" EstimatedPagesCached="78639" EstimatedAvailableDegreeOfParallelism="1" MaxCompileMemory="1285568"></OptimizerHardwareDependentProperties><OptimizerStatsUsage><StatisticsInfo LastUpdate="2022-08-06T17:26:34.64" ModificationCount="0" SamplingPercent="100" Statistics="[PK_SalesOrderHeader_SalesOrderID]" Table="[SalesOrderHeader]" Schema="[Sales]" Database="[fiddle_671bd5fd15fe479ba94b6d828d3b2594]"></StatisticsInfo><StatisticsInfo LastUpdate="2022-08-06T17:26:36.89" ModificationCount="0" SamplingPercent="100" Statistics="[IX_SalesOrderDetail_ProductID]" Table="[SalesOrderDetail]" Schema="[Sales]" Database="[fiddle_671bd5fd15fe479ba94b6d828d3b2594]"></StatisticsInfo><StatisticsInfo LastUpdate="2022-08-06T17:26:34.56" ModificationCount="0" SamplingPercent="100" Statistics="[PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID]" Table="[SalesOrderDetail]" Schema="[Sales]" Database="[fiddle_671bd5fd15fe479ba94b6d828d3b2594]"></StatisticsInfo><StatisticsInfo LastUpdate="2022-08-06T17:26:34.28" ModificationCount="0" SamplingPercent="100" Statistics="[PK_Product_ProductID]" Table="[Product]" Schema="[Production]" Database="[fiddle_671bd5fd15fe479ba94b6d828d3b2594]"></StatisticsInfo><StatisticsInfo LastUpdate="2022-08-06T17:26:36.73" ModificationCount="0" SamplingPercent="100" Statistics="[AK_SalesOrderDetail_rowguid]" Table="[SalesOrderDetail]" Schema="[Sales]" Database="[fiddle_671bd5fd15fe479ba94b6d828d3b2594]"></StatisticsInfo></OptimizerStatsUsage><TraceFlags IsCompileTime="1"><TraceFlag Value="460" Scope="Global"></TraceFlag><TraceFlag Value="8017" Scope="Global"></TraceFlag></TraceFlags><RelOp NodeId="0" PhysicalOp="Merge Join" LogicalOp="Right Outer Join" EstimateRows="121317" EstimateIO="0" EstimateCPU="0.273556" AvgRowSize="61" EstimatedTotalSubtreeCost="0.623015" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_671bd5fd15fe479ba94b6d828d3b2594]" Schema="[Production]" Table="[Product]" Alias="[P]" Column="Name"></ColumnReference></OutputList><Merge ManyToMany="0"><InnerSideJoinColumns><ColumnReference Database="[fiddle_671bd5fd15fe479ba94b6d828d3b2594]" Schema="[Sales]" Table="[SalesOrderDetail]" Alias="[SOD]" Column="ProductID"></ColumnReference></InnerSideJoinColumns><OuterSideJoinColumns><ColumnReference Database="[fiddle_671bd5fd15fe479ba94b6d828d3b2594]" Schema="[Production]" Table="[Product]" Alias="[P]" Column="ProductID"></ColumnReference></OuterSideJoinColumns><Residual><ScalarOperator ScalarString="[fiddle_671bd5fd15fe479ba94b6d828d3b2594].[Sales].[SalesOrderDetail].[ProductID] as [SOD].[ProductID]=[fiddle_671bd5fd15fe479ba94b6d828d3b2594].[Production].[Product].[ProductID] as [P].[ProductID]"><Compare CompareOp="EQ"><ScalarOperator><Identifier><ColumnReference Database="[fiddle_671bd5fd15fe479ba94b6d828d3b2594]" Schema="[Sales]" Table="[SalesOrderDetail]" Alias="[SOD]" Column="ProductID"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Identifier><ColumnReference Database="[fiddle_671bd5fd15fe479ba94b6d828d3b2594]" Schema="[Production]" Table="[Product]" Alias="[P]" Column="ProductID"></ColumnReference></Identifier></ScalarOperator></Compare></ScalarOperator></Residual><RelOp NodeId="1" PhysicalOp="Clustered Index Scan" LogicalOp="Clustered Index Scan" EstimateRows="504" EstimatedRowsRead="504" EstimateIO="0.0120139" EstimateCPU="0.0007114" AvgRowSize="65" EstimatedTotalSubtreeCost="0.0127253" TableCardinality="504" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_671bd5fd15fe479ba94b6d828d3b2594]" Schema="[Production]" Table="[Product]" Alias="[P]" Column="ProductID"></ColumnReference><ColumnReference Database="[fiddle_671bd5fd15fe479ba94b6d828d3b2594]" Schema="[Production]" Table="[Product]" Alias="[P]" Column="Name"></ColumnReference></OutputList><IndexScan Ordered="1" ScanDirection="FORWARD" ForcedIndex="0" ForceSeek="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Database="[fiddle_671bd5fd15fe479ba94b6d828d3b2594]" Schema="[Production]" Table="[Product]" Alias="[P]" Column="ProductID"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_671bd5fd15fe479ba94b6d828d3b2594]" Schema="[Production]" Table="[Product]" Alias="[P]" Column="Name"></ColumnReference></DefinedValue></DefinedValues><Object Database="[fiddle_671bd5fd15fe479ba94b6d828d3b2594]" Schema="[Production]" Table="[Product]" Index="[PK_Product_ProductID]" Alias="[P]" IndexKind="Clustered" Storage="RowStore"></Object></IndexScan></RelOp><RelOp NodeId="2" PhysicalOp="Index Scan" LogicalOp="Index Scan" EstimateRows="121317" EstimatedRowsRead="121317" EstimateIO="0.203125" EstimateCPU="0.133606" AvgRowSize="11" EstimatedTotalSubtreeCost="0.336731" TableCardinality="121317" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_671bd5fd15fe479ba94b6d828d3b2594]" Schema="[Sales]" Table="[SalesOrderDetail]" Alias="[SOD]" Column="ProductID"></ColumnReference></OutputList><IndexScan Ordered="1" ScanDirection="FORWARD" ForcedIndex="0" ForceSeek="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Database="[fiddle_671bd5fd15fe479ba94b6d828d3b2594]" Schema="[Sales]" Table="[SalesOrderDetail]" Alias="[SOD]" Column="ProductID"></ColumnReference></DefinedValue></DefinedValues><Object Database="[fiddle_671bd5fd15fe479ba94b6d828d3b2594]" Schema="[Sales]" Table="[SalesOrderDetail]" Index="[IX_SalesOrderDetail_ProductID]" Alias="[SOD]" IndexKind="NonClustered" Storage="RowStore"></Object></IndexScan></RelOp></Merge></RelOp></QueryPlan></StmtSimple></Statements></Batch></BatchSequence></ShowPlanXML>