clear markdown compare help best fiddles feedback dbanow.uk a leap of faith?
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 1130747 fiddles created (16580 in the last week).

CREATE TABLE Orders ( Order_Number int, SKU varchar(10), PRIMARY KEY(Order_Number, SKU) );
 hidden batch(es)


INSERT INTO Orders (Order_Number, SKU) VALUES (1, 'abc'), (1, 'def'), (1, 'ghi'), (2, 'abc'), (2, 'ghi'), (3, 'def'), (4, 'abc'), (4, 'def'), (4, 'ghi'), (5, 'abc') ;
10 rows affected
 hidden batch(es)


set statistics xml on; SELECT Order_Number FROM (-- Get the list of Order_Number SELECT DISTINCT Order_Number FROM Orders ) AS onr WHERE -- Check that there is an 'abc' associated EXISTS ( SELECT * FROM Orders os1 WHERE os1.Order_Number = onr.Order_Number and os1.SKU = 'abc' ) AND -- and also Check that there is an 'ghi' associated EXISTS ( SELECT * FROM Orders os2 WHERE os2.Order_Number = onr.Order_Number and os2.SKU = 'ghi' ) ORDER BY onr.Order_Number
Order_Number
1
2
4
Microsoft SQL Server 2005 XML Showplan
<ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.5" Build="13.0.4001.0"><BatchSequence><Batch><Statements><StmtSimple StatementText="SELECT&#xa; Order_Number&#xa;FROM&#xa; (-- Get the list of Order_Number&#xa; SELECT DISTINCT&#xa; Order_Number&#xa; FROM&#xa; Orders&#xa; ) AS onr&#xa;WHERE&#xa; -- Check that there is an &apos;abc&apos; associated&#xa; EXISTS&#xa; (&#xa; SELECT *&#xa; FROM Orders os1&#xa; WHERE os1.Order_Number = onr.Order_Number and os1.SKU = &apos;abc&apos;&#xa; )&#xa; AND&#xa; -- and also Check that there is an &apos;ghi&apos; associated&#xa; EXISTS&#xa; (&#xa; SELECT *&#xa; FROM Orders os2&#xa; WHERE os2.Order_Number = onr.Order_Number and os2.SKU = &apos;ghi&apos;&#xa; )&#xa;ORDER BY&#xa; onr.Order_Number" StatementId="1" StatementCompId="2" StatementType="SELECT" RetrievedFromCache="true" StatementSubTreeCost="0.0105666" StatementEstRows="1.64358" SecurityPolicyApplied="false" StatementOptmLevel="FULL" QueryHash="0xFEBC8202D525FC1A" QueryPlanHash="0x5B460C79DD03B385" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" CardinalityEstimationModelVersion="130"><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 DegreeOfParallelism="0" NonParallelPlanReason="NoParallelPlansInDesktopOrExpressEdition" CachedPlanSize="24" CompileTime="15" CompileCPU="0" CompileMemory="432"><MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0"></MemoryGrantInfo><OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="419378" EstimatedPagesCached="26211" EstimatedAvailableDegreeOfParallelism="1" MaxCompileMemory="2038112"></OptimizerHardwareDependentProperties><TraceFlags IsCompileTime="1"><TraceFlag Value="8017" Scope="Global"></TraceFlag></TraceFlags><TraceFlags IsCompileTime="0"><TraceFlag Value="8017" Scope="Global"></TraceFlag></TraceFlags><QueryTimeStats ElapsedTime="0" CpuTime="0"></QueryTimeStats><RelOp NodeId="0" PhysicalOp="Nested Loops" LogicalOp="Left Semi Join" EstimateRows="1.64358" EstimateIO="0" EstimateCPU="2.89235e-005" AvgRowSize="11" EstimatedTotalSubtreeCost="0.0105666" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_5b1f3cadbaa041df87e72f2bd77cd459]" Schema="[dbo]" Table="[Orders]" Column="Order_Number"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="3" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><NestedLoops Optimized="0"><Predicate><ScalarOperator ScalarString="[fiddle_5b1f3cadbaa041df87e72f2bd77cd459].[dbo].[Orders].[Order_Number] as [os2].[Order_Number]=[fiddle_5b1f3cadbaa041df87e72f2bd77cd459].[dbo].[Orders].[Order_Number]"><Compare CompareOp="EQ"><ScalarOperator><Identifier><ColumnReference Database="[fiddle_5b1f3cadbaa041df87e72f2bd77cd459]" Schema="[dbo]" Table="[Orders]" Alias="[os2]" Column="Order_Number"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Identifier><ColumnReference Database="[fiddle_5b1f3cadbaa041df87e72f2bd77cd459]" Schema="[dbo]" Table="[Orders]" Column="Order_Number"></ColumnReference></Identifier></ScalarOperator></Compare></ScalarOperator></Predicate><RelOp NodeId="1" PhysicalOp="Nested Loops" LogicalOp="Left Semi Join" EstimateRows="3.12829" EstimateIO="0" EstimateCPU="5.74475e-005" AvgRowSize="11" EstimatedTotalSubtreeCost="0.00703707" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_5b1f3cadbaa041df87e72f2bd77cd459]" Schema="[dbo]" Table="[Orders]" Column="Order_Number"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="4" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><NestedLoops Optimized="0"><Predicate><ScalarOperator ScalarString="[fiddle_5b1f3cadbaa041df87e72f2bd77cd459].[dbo].[Orders].[Order_Number] as [os1].[Order_Number]=[fiddle_5b1f3cadbaa041df87e72f2bd77cd459].[dbo].[Orders].[Order_Number]"><Compare CompareOp="EQ"><ScalarOperator><Identifier><ColumnReference Database="[fiddle_5b1f3cadbaa041df87e72f2bd77cd459]" Schema="[dbo]" Table="[Orders]" Alias="[os1]" Column="Order_Number"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Identifier><ColumnReference Database="[fiddle_5b1f3cadbaa041df87e72f2bd77cd459]" Schema="[dbo]" Table="[Orders]" Column="Order_Number"></ColumnReference></Identifier></ScalarOperator></Compare></ScalarOperator></Predicate><RelOp NodeId="2" PhysicalOp="Stream Aggregate" LogicalOp="Aggregate" EstimateRows="5" EstimateIO="0" EstimateCPU="7.5e-006" AvgRowSize="11" EstimatedTotalSubtreeCost="0.0033005" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_5b1f3cadbaa041df87e72f2bd77cd459]" Schema="[dbo]" Table="[Orders]" Column="Order_Number"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="5" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><StreamAggregate><DefinedValues></DefinedValues><GroupBy><ColumnReference Database="[fiddle_5b1f3cadbaa041df87e72f2bd77cd459]" Schema="[dbo]" Table="[Orders]" Column="Order_Number"></ColumnReference></GroupBy><RelOp NodeId="3" PhysicalOp="Clustered Index Scan" LogicalOp="Clustered Index Scan" EstimateRows="10" EstimatedRowsRead="10" EstimateIO="0.003125" EstimateCPU="0.000168" AvgRowSize="11" EstimatedTotalSubtreeCost="0.003293" TableCardinality="10" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_5b1f3cadbaa041df87e72f2bd77cd459]" Schema="[dbo]" Table="[Orders]" Column="Order_Number"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="10" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualScans="1" ActualLogicalReads="2" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualRowsRead="10" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><IndexScan Ordered="1" ScanDirection="FORWARD" ForcedIndex="0" ForceSeek="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Database="[fiddle_5b1f3cadbaa041df87e72f2bd77cd459]" Schema="[dbo]" Table="[Orders]" Column="Order_Number"></ColumnReference></DefinedValue></DefinedValues><Object Database="[fiddle_5b1f3cadbaa041df87e72f2bd77cd459]" Schema="[dbo]" Table="[Orders]" Index="[PK__Orders__BB665F3A6274E944]" IndexKind="Clustered" Storage="RowStore"></Object></IndexScan></RelOp></StreamAggregate></RelOp><RelOp NodeId="4" PhysicalOp="Clustered Index Scan" LogicalOp="Clustered Index Scan" EstimateRows="4" EstimatedRowsRead="10" EstimateIO="0.0032035" EstimateCPU="8.95e-005" AvgRowSize="18" EstimatedTotalSubtreeCost="0.003651" TableCardinality="10" Parallel="0" EstimateRebinds="0" EstimateRewinds="4" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_5b1f3cadbaa041df87e72f2bd77cd459]" Schema="[dbo]" Table="[Orders]" Alias="[os1]" Column="Order_Number"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="14" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualScans="1" ActualLogicalReads="11" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualRowsRead="32" ActualEndOfScans="1" ActualExecutions="5"></RunTimeCountersPerThread></RunTimeInformation><IndexScan Ordered="0" ForcedIndex="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Database="[fiddle_5b1f3cadbaa041df87e72f2bd77cd459]" Schema="[dbo]" Table="[Orders]" Alias="[os1]" Column="Order_Number"></ColumnReference></DefinedValue></DefinedValues><Object Database="[fiddle_5b1f3cadbaa041df87e72f2bd77cd459]" Schema="[dbo]" Table="[Orders]" Index="[PK__Orders__BB665F3A6274E944]" Alias="[os1]" IndexKind="Clustered" Storage="RowStore"></Object><Predicate><ScalarOperator ScalarString="[fiddle_5b1f3cadbaa041df87e72f2bd77cd459].[dbo].[Orders].[SKU] as [os1].[SKU]=&apos;abc&apos;"><Compare CompareOp="EQ"><ScalarOperator><Identifier><ColumnReference Database="[fiddle_5b1f3cadbaa041df87e72f2bd77cd459]" Schema="[dbo]" Table="[Orders]" Alias="[os1]" Column="SKU"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="&apos;abc&apos;"></Const></ScalarOperator></Compare></ScalarOperator></Predicate></IndexScan></RelOp></NestedLoops></RelOp><RelOp NodeId="5" PhysicalOp="Clustered Index Scan" LogicalOp="Clustered Index Scan" EstimateRows="3" EstimatedRowsRead="10" EstimateIO="0.0032035" EstimateCPU="8.95e-005" AvgRowSize="20" EstimatedTotalSubtreeCost="0.00348348" TableCardinality="10" Parallel="0" EstimateRebinds="0" EstimateRewinds="2.12829" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_5b1f3cadbaa041df87e72f2bd77cd459]" Schema="[dbo]" Table="[Orders]" Alias="[os2]" Column="Order_Number"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="9" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualScans="1" ActualLogicalReads="9" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualRowsRead="27" ActualEndOfScans="1" ActualExecutions="4"></RunTimeCountersPerThread></RunTimeInformation><IndexScan Ordered="0" ForcedIndex="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Database="[fiddle_5b1f3cadbaa041df87e72f2bd77cd459]" Schema="[dbo]" Table="[Orders]" Alias="[os2]" Column="Order_Number"></ColumnReference></DefinedValue></DefinedValues><Object Database="[fiddle_5b1f3cadbaa041df87e72f2bd77cd459]" Schema="[dbo]" Table="[Orders]" Index="[PK__Orders__BB665F3A6274E944]" Alias="[os2]" IndexKind="Clustered" Storage="RowStore"></Object><Predicate><ScalarOperator ScalarString="[fiddle_5b1f3cadbaa041df87e72f2bd77cd459].[dbo].[Orders].[SKU] as [os2].[SKU]=&apos;ghi&apos;"><Compare CompareOp="EQ"><ScalarOperator><Identifier><ColumnReference Database="[fiddle_5b1f3cadbaa041df87e72f2bd77cd459]" Schema="[dbo]" Table="[Orders]" Alias="[os2]" Column="SKU"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="&apos;ghi&apos;"></Const></ScalarOperator></Compare></ScalarOperator></Predicate></IndexScan></RelOp></NestedLoops></RelOp></QueryPlan></StmtSimple></Statements></Batch></BatchSequence></ShowPlanXML>
 hidden batch(es)


set statistics xml on; SELECT onr.Order_Number FROM (SELECT DISTINCT Order_Number FROM Orders ) AS onr JOIN Orders AS sk1 ON sk1.Order_Number = onr.Order_Number AND sk1.SKU = 'abc' JOIN Orders AS sk2 ON sk2.Order_Number = onr.Order_Number AND sk2.SKU = 'ghi' ORDER BY onr.Order_Number
Order_Number
1
2
4
Microsoft SQL Server 2005 XML Showplan
<ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.5" Build="13.0.4001.0"><BatchSequence><Batch><Statements><StmtSimple StatementText="SELECT&#xa; onr.Order_Number&#xa;FROM&#xa; (SELECT DISTINCT &#xa; Order_Number &#xa; FROM &#xa; Orders&#xa; ) AS onr&#xa; JOIN Orders AS sk1 ON sk1.Order_Number = onr.Order_Number AND sk1.SKU = &apos;abc&apos;&#xa; JOIN Orders AS sk2 ON sk2.Order_Number = onr.Order_Number AND sk2.SKU = &apos;ghi&apos;&#xa;ORDER BY&#xa; onr.Order_Number" StatementId="1" StatementCompId="2" StatementType="SELECT" RetrievedFromCache="true" StatementSubTreeCost="0.0105393" StatementEstRows="2.55" SecurityPolicyApplied="false" StatementOptmLevel="FULL" QueryHash="0x637A5D034E4BA047" QueryPlanHash="0x4141E8D75F6BFBE2" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" CardinalityEstimationModelVersion="130"><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 DegreeOfParallelism="0" NonParallelPlanReason="NoParallelPlansInDesktopOrExpressEdition" CachedPlanSize="24" CompileTime="0" CompileCPU="0" CompileMemory="344"><MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0"></MemoryGrantInfo><OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="419378" EstimatedPagesCached="26211" EstimatedAvailableDegreeOfParallelism="1" MaxCompileMemory="2038112"></OptimizerHardwareDependentProperties><TraceFlags IsCompileTime="1"><TraceFlag Value="8017" Scope="Global"></TraceFlag></TraceFlags><TraceFlags IsCompileTime="0"><TraceFlag Value="8017" Scope="Global"></TraceFlag></TraceFlags><QueryTimeStats ElapsedTime="0" CpuTime="0"></QueryTimeStats><RelOp NodeId="0" PhysicalOp="Nested Loops" LogicalOp="Inner Join" EstimateRows="2.55" EstimateIO="0" EstimateCPU="5.3295e-005" AvgRowSize="11" EstimatedTotalSubtreeCost="0.0105393" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_5b1f3cadbaa041df87e72f2bd77cd459]" Schema="[dbo]" Table="[Orders]" Column="Order_Number"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="3" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><NestedLoops Optimized="0"><Predicate><ScalarOperator ScalarString="[fiddle_5b1f3cadbaa041df87e72f2bd77cd459].[dbo].[Orders].[Order_Number] as [sk2].[Order_Number]=[fiddle_5b1f3cadbaa041df87e72f2bd77cd459].[dbo].[Orders].[Order_Number]"><Compare CompareOp="EQ"><ScalarOperator><Identifier><ColumnReference Database="[fiddle_5b1f3cadbaa041df87e72f2bd77cd459]" Schema="[dbo]" Table="[Orders]" Alias="[sk2]" Column="Order_Number"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Identifier><ColumnReference Database="[fiddle_5b1f3cadbaa041df87e72f2bd77cd459]" Schema="[dbo]" Table="[Orders]" Column="Order_Number"></ColumnReference></Identifier></ScalarOperator></Compare></ScalarOperator></Predicate><RelOp NodeId="1" PhysicalOp="Nested Loops" LogicalOp="Inner Join" EstimateRows="2.55" EstimateIO="0" EstimateCPU="1.254e-005" AvgRowSize="15" EstimatedTotalSubtreeCost="0.00690964" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_5b1f3cadbaa041df87e72f2bd77cd459]" Schema="[dbo]" Table="[Orders]" Alias="[sk1]" Column="Order_Number"></ColumnReference><ColumnReference Database="[fiddle_5b1f3cadbaa041df87e72f2bd77cd459]" Schema="[dbo]" Table="[Orders]" Alias="[sk2]" Column="Order_Number"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="3" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><NestedLoops Optimized="0"><OuterReferences><ColumnReference Database="[fiddle_5b1f3cadbaa041df87e72f2bd77cd459]" Schema="[dbo]" Table="[Orders]" Alias="[sk2]" Column="Order_Number"></ColumnReference></OuterReferences><RelOp NodeId="2" PhysicalOp="Clustered Index Scan" LogicalOp="Clustered Index Scan" EstimateRows="3" EstimatedRowsRead="10" EstimateIO="0.003125" EstimateCPU="0.000168" AvgRowSize="20" EstimatedTotalSubtreeCost="0.003293" TableCardinality="10" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_5b1f3cadbaa041df87e72f2bd77cd459]" Schema="[dbo]" Table="[Orders]" Alias="[sk2]" Column="Order_Number"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="3" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualScans="1" ActualLogicalReads="2" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualRowsRead="10" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><IndexScan Ordered="1" ScanDirection="FORWARD" ForcedIndex="0" ForceSeek="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Database="[fiddle_5b1f3cadbaa041df87e72f2bd77cd459]" Schema="[dbo]" Table="[Orders]" Alias="[sk2]" Column="Order_Number"></ColumnReference></DefinedValue></DefinedValues><Object Database="[fiddle_5b1f3cadbaa041df87e72f2bd77cd459]" Schema="[dbo]" Table="[Orders]" Index="[PK__Orders__BB665F3A6274E944]" Alias="[sk2]" IndexKind="Clustered" Storage="RowStore"></Object><Predicate><ScalarOperator ScalarString="[fiddle_5b1f3cadbaa041df87e72f2bd77cd459].[dbo].[Orders].[SKU] as [sk2].[SKU]=&apos;ghi&apos;"><Compare CompareOp="EQ"><ScalarOperator><Identifier><ColumnReference Database="[fiddle_5b1f3cadbaa041df87e72f2bd77cd459]" Schema="[dbo]" Table="[Orders]" Alias="[sk2]" Column="SKU"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="&apos;ghi&apos;"></Const></ScalarOperator></Compare></ScalarOperator></Predicate></IndexScan></RelOp><RelOp NodeId="3" PhysicalOp="Clustered Index Seek" LogicalOp="Clustered Index Seek" EstimateRows="1" EstimatedRowsRead="1" EstimateIO="0.003125" EstimateCPU="0.0001581" AvgRowSize="11" EstimatedTotalSubtreeCost="0.0035993" TableCardinality="10" Parallel="0" EstimateRebinds="2" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_5b1f3cadbaa041df87e72f2bd77cd459]" Schema="[dbo]" Table="[Orders]" Alias="[sk1]" Column="Order_Number"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="3" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualScans="0" ActualLogicalReads="6" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualRowsRead="3" ActualEndOfScans="0" ActualExecutions="3"></RunTimeCountersPerThread></RunTimeInformation><IndexScan Ordered="1" ScanDirection="FORWARD" ForcedIndex="0" ForceSeek="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Database="[fiddle_5b1f3cadbaa041df87e72f2bd77cd459]" Schema="[dbo]" Table="[Orders]" Alias="[sk1]" Column="Order_Number"></ColumnReference></DefinedValue></DefinedValues><Object Database="[fiddle_5b1f3cadbaa041df87e72f2bd77cd459]" Schema="[dbo]" Table="[Orders]" Index="[PK__Orders__BB665F3A6274E944]" Alias="[sk1]" IndexKind="Clustered" Storage="RowStore"></Object><SeekPredicates><SeekPredicateNew><SeekKeys><Prefix ScanType="EQ"><RangeColumns><ColumnReference Database="[fiddle_5b1f3cadbaa041df87e72f2bd77cd459]" Schema="[dbo]" Table="[Orders]" Alias="[sk1]" Column="Order_Number"></ColumnReference><ColumnReference Database="[fiddle_5b1f3cadbaa041df87e72f2bd77cd459]" Schema="[dbo]" Table="[Orders]" Alias="[sk1]" Column="SKU"></ColumnReference></RangeColumns><RangeExpressions><ScalarOperator ScalarString="[fiddle_5b1f3cadbaa041df87e72f2bd77cd459].[dbo].[Orders].[Order_Number] as [sk2].[Order_Number]"><Identifier><ColumnReference Database="[fiddle_5b1f3cadbaa041df87e72f2bd77cd459]" Schema="[dbo]" Table="[Orders]" Alias="[sk2]" Column="Order_Number"></ColumnReference></Identifier></ScalarOperator><ScalarOperator ScalarString="&apos;abc&apos;"><Const ConstValue="&apos;abc&apos;"></Const></ScalarOperator></RangeExpressions></Prefix></SeekKeys></SeekPredicateNew></SeekPredicates></IndexScan></RelOp></NestedLoops></RelOp><RelOp NodeId="4" PhysicalOp="Stream Aggregate" LogicalOp="Aggregate" EstimateRows="5" EstimateIO="0" EstimateCPU="7.5e-006" AvgRowSize="11" EstimatedTotalSubtreeCost="0.00357253" Parallel="0" EstimateRebinds="0" EstimateRewinds="1.55" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_5b1f3cadbaa041df87e72f2bd77cd459]" Schema="[dbo]" Table="[Orders]" Column="Order_Number"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="15" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="3" ActualExecutions="3"></RunTimeCountersPerThread></RunTimeInformation><StreamAggregate><DefinedValues></DefinedValues><GroupBy><ColumnReference Database="[fiddle_5b1f3cadbaa041df87e72f2bd77cd459]" Schema="[dbo]" Table="[Orders]" Column="Order_Number"></ColumnReference></GroupBy><RelOp NodeId="5" PhysicalOp="Clustered Index Scan" LogicalOp="Clustered Index Scan" EstimateRows="10" EstimatedRowsRead="10" EstimateIO="0.003125" EstimateCPU="0.000168" AvgRowSize="11" EstimatedTotalSubtreeCost="0.0035534" TableCardinality="10" Parallel="0" EstimateRebinds="0" EstimateRewinds="1.55" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_5b1f3cadbaa041df87e72f2bd77cd459]" Schema="[dbo]" Table="[Orders]" Column="Order_Number"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="30" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualScans="1" ActualLogicalReads="7" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualRowsRead="30" ActualEndOfScans="3" ActualExecutions="3"></RunTimeCountersPerThread></RunTimeInformation><IndexScan Ordered="1" ScanDirection="FORWARD" ForcedIndex="0" ForceSeek="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Database="[fiddle_5b1f3cadbaa041df87e72f2bd77cd459]" Schema="[dbo]" Table="[Orders]" Column="Order_Number"></ColumnReference></DefinedValue></DefinedValues><Object Database="[fiddle_5b1f3cadbaa041df87e72f2bd77cd459]" Schema="[dbo]" Table="[Orders]" Index="[PK__Orders__BB665F3A6274E944]" IndexKind="Clustered" Storage="RowStore"></Object></IndexScan></RelOp></StreamAggregate></RelOp></NestedLoops></RelOp></QueryPlan></StmtSimple></Statements></Batch></BatchSequence></ShowPlanXML>
 hidden batch(es)


CREATE UNIQUE INDEX sku_order_number ON Orders (SKU, Order_Number) ;
Microsoft SQL Server 2005 XML Showplan
<ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.5" Build="13.0.4001.0"><BatchSequence><Batch><Statements><StmtSimple StatementText="insert [dbo].[Orders] select * from [dbo].[Orders] option (maxdop 1)" StatementId="1" StatementCompId="1" StatementType="INSERT" RetrievedFromCache="false" StatementSubTreeCost="0.0247161" StatementEstRows="10" SecurityPolicyApplied="false" StatementOptmLevel="FULL" QueryHash="0x50EAE7D13AD1500B" QueryPlanHash="0x96E9C9A41BCC5E0" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" CardinalityEstimationModelVersion="130"><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 DegreeOfParallelism="0" MemoryGrant="1024" NonParallelPlanReason="MaxDOPSetToOne" CachedPlanSize="16" CompileTime="0" CompileCPU="0" CompileMemory="112"><Warnings><MemoryGrantWarning GrantWarningKind="Excessive Grant" RequestedMemory="1024" GrantedMemory="1024" MaxUsedMemory="16"></MemoryGrantWarning></Warnings><MemoryGrantInfo SerialRequiredMemory="512" SerialDesiredMemory="544" RequiredMemory="512" DesiredMemory="544" RequestedMemory="1024" GrantWaitTime="0" GrantedMemory="1024" MaxUsedMemory="16" MaxQueryMemory="614240"></MemoryGrantInfo><OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="419378" EstimatedPagesCached="26211" EstimatedAvailableDegreeOfParallelism="1" MaxCompileMemory="2038112"></OptimizerHardwareDependentProperties><TraceFlags IsCompileTime="1"><TraceFlag Value="8017" Scope="Global"></TraceFlag></TraceFlags><TraceFlags IsCompileTime="0"><TraceFlag Value="8017" Scope="Global"></TraceFlag></TraceFlags><QueryTimeStats ElapsedTime="0" CpuTime="0"></QueryTimeStats><RelOp NodeId="0" PhysicalOp="Index Insert" LogicalOp="Insert" EstimateRows="10" EstimateIO="0.01" EstimateCPU="1e-005" AvgRowSize="9" EstimatedTotalSubtreeCost="0.0247161" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="0" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualScans="0" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><CreateIndex><Object Database="[fiddle_5b1f3cadbaa041df87e72f2bd77cd459]" Schema="[dbo]" Table="[Orders]" Index="[sku_order_number]" IndexKind="NonClustered" Storage="RowStore"></Object><RelOp NodeId="1" PhysicalOp="Sort" LogicalOp="Sort" EstimateRows="10" EstimateIO="0.0112613" EstimateCPU="0.000151842" AvgRowSize="20" EstimatedTotalSubtreeCost="0.0147061" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Column="RowRefSrc1006"></ColumnReference></OutputList><MemoryFractions Input="1" Output="1"></MemoryFractions><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="10" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualScans="0" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualRebinds="1" ActualRewinds="0" ActualEndOfScans="1" ActualExecutions="1" InputMemoryGrant="832" OutputMemoryGrant="1024" UsedMemoryGrant="16"></RunTimeCountersPerThread></RunTimeInformation><Sort Distinct="0"><OrderBy><OrderByColumn Ascending="1"><ColumnReference Database="[fiddle_5b1f3cadbaa041df87e72f2bd77cd459]" Schema="[dbo]" Table="[Orders]" Column="SKU"></ColumnReference></OrderByColumn><OrderByColumn Ascending="1"><ColumnReference Database="[fiddle_5b1f3cadbaa041df87e72f2bd77cd459]" Schema="[dbo]" Table="[Orders]" Column="Order_Number"></ColumnReference></OrderByColumn></OrderBy><RelOp NodeId="2" PhysicalOp="Clustered Index Scan" LogicalOp="Clustered Index Scan" EstimateRows="10" EstimatedRowsRead="10" EstimateIO="0.003125" EstimateCPU="0.000168" AvgRowSize="20" EstimatedTotalSubtreeCost="0.003293" TableCardinality="10" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_5b1f3cadbaa041df87e72f2bd77cd459]" Schema="[dbo]" Table="[Orders]" Column="Order_Number"></ColumnReference><ColumnReference Database="[fiddle_5b1f3cadbaa041df87e72f2bd77cd459]" Schema="[dbo]" Table="[Orders]" Column="SKU"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="10" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualScans="1" ActualLogicalReads="2" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualRowsRead="10" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><IndexScan Ordered="0" ForcedIndex="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Database="[fiddle_5b1f3cadbaa041df87e72f2bd77cd459]" Schema="[dbo]" Table="[Orders]" Column="Order_Number"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_5b1f3cadbaa041df87e72f2bd77cd459]" Schema="[dbo]" Table="[Orders]" Column="SKU"></ColumnReference></DefinedValue></DefinedValues><Object Database="[fiddle_5b1f3cadbaa041df87e72f2bd77cd459]" Schema="[dbo]" Table="[Orders]" Index="[PK__Orders__BB665F3A6274E944]" IndexKind="Clustered" Storage="RowStore"></Object></IndexScan></RelOp></Sort></RelOp></CreateIndex></RelOp></QueryPlan></StmtSimple></Statements></Batch></BatchSequence></ShowPlanXML>
 hidden batch(es)


set statistics xml on; SELECT sk1.Order_Number FROM dbo.Orders AS sk1 JOIN dbo.Orders AS sk2 ON sk1.Order_Number = sk2.Order_Number AND sk1.SKU = 'abc' AND sk2.SKU = 'ghi' ;
Order_Number
1
2
4
Microsoft SQL Server 2005 XML Showplan
<ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.5" Build="13.0.4001.0"><BatchSequence><Batch><Statements><StmtSimple StatementText="SELECT&#xa; sk1.Order_Number&#xa;FROM&#xa; dbo.Orders AS sk1&#xa; JOIN &#xa; dbo.Orders AS sk2 &#xa; ON sk1.Order_Number = sk2.Order_Number&#xa; AND sk1.SKU = &apos;abc&apos;&#xa; AND sk2.SKU = &apos;ghi&apos;" StatementId="1" StatementCompId="2" StatementType="SELECT" RetrievedFromCache="true" StatementSubTreeCost="0.00689714" StatementEstRows="2.55" SecurityPolicyApplied="false" StatementOptmLevel="FULL" QueryHash="0xAA1B5EDD5A463F0E" QueryPlanHash="0x7BD206507FCA1641" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" CardinalityEstimationModelVersion="130"><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 DegreeOfParallelism="0" NonParallelPlanReason="NoParallelPlansInDesktopOrExpressEdition" CachedPlanSize="16" CompileTime="0" CompileCPU="0" CompileMemory="224"><MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0"></MemoryGrantInfo><OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="419378" EstimatedPagesCached="26211" EstimatedAvailableDegreeOfParallelism="1" MaxCompileMemory="2038112"></OptimizerHardwareDependentProperties><TraceFlags IsCompileTime="1"><TraceFlag Value="8017" Scope="Global"></TraceFlag></TraceFlags><TraceFlags IsCompileTime="0"><TraceFlag Value="8017" Scope="Global"></TraceFlag></TraceFlags><QueryTimeStats ElapsedTime="0" CpuTime="0"></QueryTimeStats><RelOp NodeId="0" PhysicalOp="Nested Loops" LogicalOp="Inner Join" EstimateRows="2.55" EstimateIO="0" EstimateCPU="1.254e-005" AvgRowSize="11" EstimatedTotalSubtreeCost="0.00689714" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_5b1f3cadbaa041df87e72f2bd77cd459]" Schema="[dbo]" Table="[Orders]" Alias="[sk1]" Column="Order_Number"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="3" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><NestedLoops Optimized="0"><OuterReferences><ColumnReference Database="[fiddle_5b1f3cadbaa041df87e72f2bd77cd459]" Schema="[dbo]" Table="[Orders]" Alias="[sk2]" Column="Order_Number"></ColumnReference></OuterReferences><RelOp NodeId="1" PhysicalOp="Index Seek" LogicalOp="Index Seek" EstimateRows="3" EstimatedRowsRead="3" EstimateIO="0.003125" EstimateCPU="0.0001603" AvgRowSize="11" EstimatedTotalSubtreeCost="0.0032853" TableCardinality="10" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_5b1f3cadbaa041df87e72f2bd77cd459]" Schema="[dbo]" Table="[Orders]" Alias="[sk2]" Column="Order_Number"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="3" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualScans="1" ActualLogicalReads="2" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualRowsRead="3" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><IndexScan Ordered="1" ScanDirection="FORWARD" ForcedIndex="0" ForceSeek="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Database="[fiddle_5b1f3cadbaa041df87e72f2bd77cd459]" Schema="[dbo]" Table="[Orders]" Alias="[sk2]" Column="Order_Number"></ColumnReference></DefinedValue></DefinedValues><Object Database="[fiddle_5b1f3cadbaa041df87e72f2bd77cd459]" Schema="[dbo]" Table="[Orders]" Index="[sku_order_number]" Alias="[sk2]" IndexKind="NonClustered" Storage="RowStore"></Object><SeekPredicates><SeekPredicateNew><SeekKeys><Prefix ScanType="EQ"><RangeColumns><ColumnReference Database="[fiddle_5b1f3cadbaa041df87e72f2bd77cd459]" Schema="[dbo]" Table="[Orders]" Alias="[sk2]" Column="SKU"></ColumnReference></RangeColumns><RangeExpressions><ScalarOperator ScalarString="&apos;ghi&apos;"><Const ConstValue="&apos;ghi&apos;"></Const></ScalarOperator></RangeExpressions></Prefix></SeekKeys></SeekPredicateNew></SeekPredicates></IndexScan></RelOp><RelOp NodeId="2" PhysicalOp="Index Seek" LogicalOp="Index Seek" EstimateRows="1" EstimatedRowsRead="1" EstimateIO="0.003125" EstimateCPU="0.0001581" AvgRowSize="11" EstimatedTotalSubtreeCost="0.0035993" TableCardinality="10" Parallel="0" EstimateRebinds="2" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_5b1f3cadbaa041df87e72f2bd77cd459]" Schema="[dbo]" Table="[Orders]" Alias="[sk1]" Column="Order_Number"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="3" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualScans="0" ActualLogicalReads="6" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualRowsRead="3" ActualEndOfScans="0" ActualExecutions="3"></RunTimeCountersPerThread></RunTimeInformation><IndexScan Ordered="1" ScanDirection="FORWARD" ForcedIndex="0" ForceSeek="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Database="[fiddle_5b1f3cadbaa041df87e72f2bd77cd459]" Schema="[dbo]" Table="[Orders]" Alias="[sk1]" Column="Order_Number"></ColumnReference></DefinedValue></DefinedValues><Object Database="[fiddle_5b1f3cadbaa041df87e72f2bd77cd459]" Schema="[dbo]" Table="[Orders]" Index="[sku_order_number]" Alias="[sk1]" IndexKind="NonClustered" Storage="RowStore"></Object><SeekPredicates><SeekPredicateNew><SeekKeys><Prefix ScanType="EQ"><RangeColumns><ColumnReference Database="[fiddle_5b1f3cadbaa041df87e72f2bd77cd459]" Schema="[dbo]" Table="[Orders]" Alias="[sk1]" Column="SKU"></ColumnReference><ColumnReference Database="[fiddle_5b1f3cadbaa041df87e72f2bd77cd459]" Schema="[dbo]" Table="[Orders]" Alias="[sk1]" Column="Order_Number"></ColumnReference></RangeColumns><RangeExpressions><ScalarOperator ScalarString="&apos;abc&apos;"><Const ConstValue="&apos;abc&apos;"></Const></ScalarOperator><ScalarOperator ScalarString="[fiddle_5b1f3cadbaa041df87e72f2bd77cd459].[dbo].[Orders].[Order_Number] as [sk2].[Order_Number]"><Identifier><ColumnReference Database="[fiddle_5b1f3cadbaa041df87e72f2bd77cd459]" Schema="[dbo]" Table="[Orders]" Alias="[sk2]" Column="Order_Number"></ColumnReference></Identifier></ScalarOperator></RangeExpressions></Prefix></SeekKeys></SeekPredicateNew></SeekPredicates></IndexScan></RelOp></NestedLoops></RelOp></QueryPlan></StmtSimple></Statements></Batch></BatchSequence></ShowPlanXML>
 hidden batch(es)