SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE dbo.ProductPrices
(
    Id bigint NOT NULL
    CONSTRAINT pk_product_prices
        PRIMARY KEY,
    ItemId varchar(50) NOT NULL,
    [Group] varchar(10) NOT NULL,
    [Value] decimal(18,4) NOT NULL,
    Color varchar(50)
);
CREATE TABLE dbo.Products
(
    ProductId nvarchar(70) NOT NULL
        CONSTRAINT PK_table_4
            PRIMARY KEY,
    ItemId nvarchar(50) NOT NULL,
    Color nvarchar(50) NOT NULL
);
INSERT dbo.Products
    (ProductId, ItemId, Color)
VALUES
    (N'1', N'X', '5N'),
    (N'2', N'X', '10N'),
    (N'3', N'X', '2N'),
    (N'4', N'Y', '12S');

INSERT dbo.ProductPrices
    (Id, ItemId, [Group], Color, [Value])
VALUES
    (1, 'X', 'T', NULL, 5),
    (2, 'X', 'T', '5N', 7),
    (3, 'X', 'M', '2N', 9),
    (4, 'X', 'M', NULL, 6),
    (5, 'Y', 'M', NULL, 12);
9 rows affected

CREATE INDEX [IX dbo.ProductPrices ItemId, Group (Color)]
ON dbo.ProductPrices
(
    ItemId,
    [Group],
    Color
)
INCLUDE
(
    [Value]
);
set statistics xml on;

SELECT
    P.ProductId,
    P.ItemId,
    G.[Group],
    P.Color,
    V.[Value]
FROM dbo.Products AS P
CROSS APPLY 
(
    SELECT DISTINCT
        PP.[Group]
    FROM dbo.ProductPrices AS PP
    WHERE
        PP.ItemId = P.ItemId
) AS G
CROSS APPLY 
(
    SELECT TOP (1)
        PP2.[Value]
    FROM dbo.ProductPrices AS PP2
    WHERE
        PP2.ItemId = P.ItemId
        AND PP2.[Group] = G.[Group]
    ORDER BY
        CASE
            WHEN PP2.Color = P.Color THEN 1
            WHEN PP2.Color IS NULL THEN 2
            ELSE 3
        END
) AS V
ORDER BY 
    P.ProductId,
    P.ItemId,
    G.[Group] DESC;


ProductId
ItemId
Group
Color
Value


1
X
T
5N
7.0000

1
X
M
5N
6.0000

2
X
T
10N
5.0000

2
X
M
10N
6.0000

3
X
T
2N
5.0000

3
X
M
2N
9.0000

4
Y
M
12S
12.0000




Microsoft SQL Server 2005 XML Showplan


<ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.539" Build="15.0.4003.23"><BatchSequence><Batch><Statements><StmtSimple StatementText="SELECT&#xa;    P.ProductId,&#xa;    P.ItemId,&#xa;    G.[Group],&#xa;    P.Color,&#xa;    V.[Value]&#xa;FROM dbo.Products AS P&#xa;CROSS APPLY &#xa;(&#xa;    SELECT DISTINCT&#xa;        PP.[Group]&#xa;    FROM dbo.ProductPrices AS PP&#xa;    WHERE&#xa;        PP.ItemId = P.ItemId&#xa;) AS G&#xa;CROSS APPLY &#xa;(&#xa;    SELECT TOP (1)&#xa;        PP2.[Value]&#xa;    FROM dbo.ProductPrices AS PP2&#xa;    WHERE&#xa;        PP2.ItemId = P.ItemId&#xa;        AND PP2.[Group] = G.[Group]&#xa;    ORDER BY&#xa;        CASE&#xa;            WHEN PP2.Color = P.Color THEN 1&#xa;            WHEN PP2.Color IS NULL THEN 2&#xa;            ELSE 3&#xa;        END&#xa;) AS V&#xa;ORDER BY &#xa;    P.ProductId,&#xa;    P.ItemId,&#xa;    G.[Group] DESC" StatementId="1" StatementCompId="2" StatementType="SELECT" RetrievedFromCache="true" StatementSubTreeCost="0.141119" StatementEstRows="7.64969" SecurityPolicyApplied="false" StatementOptmLevel="FULL" QueryHash="0x7D0C321DDB3A8CC6" QueryPlanHash="0x0BDF177A1BC7478C" 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 DegreeOfParallelism="0" MemoryGrant="1024" NonParallelPlanReason="NoParallelPlansInDesktopOrExpressEdition" CachedPlanSize="56" CompileTime="0" CompileCPU="0" CompileMemory="520"><MemoryGrantInfo SerialRequiredMemory="528" SerialDesiredMemory="568" RequiredMemory="528" DesiredMemory="568" RequestedMemory="1024" GrantWaitTime="0" MaxQueryMemory="769008" GrantedMemory="1024" MaxUsedMemory="32"></MemoryGrantInfo><OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="838808" EstimatedPagesCached="52425" EstimatedAvailableDegreeOfParallelism="1" MaxCompileMemory="2548112"></OptimizerHardwareDependentProperties><OptimizerStatsUsage><StatisticsInfo LastUpdate="2020-03-17T14:39:40.02" ModificationCount="0" SamplingPercent="100" Statistics="[IX dbo.ProductPrices ItemId, Group (Color)]" Table="[ProductPrices]" Schema="[dbo]" Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]"></StatisticsInfo><StatisticsInfo LastUpdate="2020-03-17T14:39:40.02" ModificationCount="0" SamplingPercent="100" Statistics="[_WA_Sys_00000002_24927208]" Table="[Products]" Schema="[dbo]" Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]"></StatisticsInfo><StatisticsInfo LastUpdate="2020-03-17T14:39:40.02" ModificationCount="0" SamplingPercent="100" Statistics="[_WA_Sys_00000003_22AA2996]" Table="[ProductPrices]" Schema="[dbo]" Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]"></StatisticsInfo><StatisticsInfo LastUpdate="2020-03-17T14:39:40.02" ModificationCount="0" SamplingPercent="100" Statistics="[_WA_Sys_00000003_24927208]" Table="[Products]" Schema="[dbo]" Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]"></StatisticsInfo></OptimizerStatsUsage><TraceFlags IsCompileTime="1"><TraceFlag Value="460" Scope="Global"></TraceFlag><TraceFlag Value="8017" Scope="Global"></TraceFlag></TraceFlags><TraceFlags IsCompileTime="0"><TraceFlag Value="460" Scope="Global"></TraceFlag><TraceFlag Value="8017" Scope="Global"></TraceFlag></TraceFlags><QueryTimeStats ElapsedTime="15" CpuTime="15"></QueryTimeStats><RelOp NodeId="0" PhysicalOp="Nested Loops" LogicalOp="Inner Join" EstimateRows="7.64969" EstimateIO="0" EstimateCPU="3.19757e-05" AvgRowSize="104" EstimatedTotalSubtreeCost="0.141119" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[Products]" Alias="[P]" Column="ProductId"></ColumnReference><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[Products]" Alias="[P]" Column="ItemId"></ColumnReference><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[Products]" Alias="[P]" Column="Color"></ColumnReference><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[ProductPrices]" Alias="[PP]" Column="Group"></ColumnReference><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[ProductPrices]" Alias="[PP2]" Column="Value"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="7" Batches="0" ActualExecutionMode="Row" ActualElapsedms="15" ActualCPUms="15" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><NestedLoops Optimized="0"><OuterReferences><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[Products]" Alias="[P]" Column="ItemId"></ColumnReference><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[Products]" Alias="[P]" Column="Color"></ColumnReference><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[ProductPrices]" Alias="[PP]" Column="Group"></ColumnReference></OuterReferences><RelOp NodeId="1" PhysicalOp="Nested Loops" LogicalOp="Inner Join" EstimateRows="7.64969" EstimateIO="0" EstimateCPU="3.19757e-05" AvgRowSize="95" EstimatedTotalSubtreeCost="0.0523352" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[Products]" Alias="[P]" Column="ProductId"></ColumnReference><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[Products]" Alias="[P]" Column="ItemId"></ColumnReference><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[Products]" Alias="[P]" Column="Color"></ColumnReference><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[ProductPrices]" Alias="[PP]" Column="Group"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="7" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><NestedLoops Optimized="0"><OuterReferences><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[Products]" Alias="[P]" Column="ItemId"></ColumnReference></OuterReferences><RelOp NodeId="2" PhysicalOp="Clustered Index Scan" LogicalOp="Clustered Index Scan" EstimateRows="4" EstimatedRowsRead="4" EstimateIO="0.003125" EstimateCPU="0.0001614" AvgRowSize="92" EstimatedTotalSubtreeCost="0.0032864" TableCardinality="4" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[Products]" Alias="[P]" Column="ProductId"></ColumnReference><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[Products]" Alias="[P]" Column="ItemId"></ColumnReference><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[Products]" Alias="[P]" Column="Color"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="4" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualScans="1" ActualLogicalReads="2" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualRowsRead="4" 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_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[Products]" Alias="[P]" Column="ProductId"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[Products]" Alias="[P]" Column="ItemId"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[Products]" Alias="[P]" Column="Color"></ColumnReference></DefinedValue></DefinedValues><Object Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[Products]" Index="[PK_table_4]" Alias="[P]" IndexKind="Clustered" Storage="RowStore"></Object></IndexScan></RelOp><RelOp NodeId="3" PhysicalOp="Sort" LogicalOp="Distinct Sort" EstimateRows="1.91242" EstimateIO="0.0112613" EstimateCPU="0.000105168" AvgRowSize="12" EstimatedTotalSubtreeCost="0.0490168" Parallel="0" EstimateRebinds="1.5" EstimateRewinds="1.5" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[ProductPrices]" Alias="[PP]" Column="Group"></ColumnReference></OutputList><MemoryFractions Input="0.8" Output="0.8"></MemoryFractions><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="7" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualScans="0" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualRebinds="2" ActualRewinds="2" ActualEndOfScans="4" ActualExecutions="4" InputMemoryGrant="904" OutputMemoryGrant="520" UsedMemoryGrant="16"></RunTimeCountersPerThread></RunTimeInformation><Sort Distinct="1"><OrderBy><OrderByColumn Ascending="0"><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[ProductPrices]" Alias="[PP]" Column="Group"></ColumnReference></OrderByColumn></OrderBy><RelOp NodeId="5" PhysicalOp="Clustered Index Scan" LogicalOp="Clustered Index Scan" EstimateRows="2.5" EstimatedRowsRead="5" EstimateIO="0.0032035" EstimateCPU="8.4e-05" AvgRowSize="15" EstimatedTotalSubtreeCost="0.0035395" TableCardinality="5" Parallel="0" EstimateRebinds="0" EstimateRewinds="3" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[ProductPrices]" Alias="[PP]" Column="Group"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="13" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualScans="4" ActualLogicalReads="8" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualRowsRead="20" ActualEndOfScans="4" ActualExecutions="4"></RunTimeCountersPerThread></RunTimeInformation><IndexScan Ordered="0" ForcedIndex="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[ProductPrices]" Alias="[PP]" Column="Group"></ColumnReference></DefinedValue></DefinedValues><Object Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[ProductPrices]" Index="[pk_product_prices]" Alias="[PP]" IndexKind="Clustered" Storage="RowStore"></Object><Predicate><ScalarOperator ScalarString="CONVERT_IMPLICIT(nvarchar(50),[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b].[dbo].[ProductPrices].[ItemId] as [PP].[ItemId],0)=[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b].[dbo].[Products].[ItemId] as [P].[ItemId]"><Compare CompareOp="EQ"><ScalarOperator><Convert DataType="nvarchar" Length="100" Style="0" Implicit="1"><ScalarOperator><Identifier><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[ProductPrices]" Alias="[PP]" Column="ItemId"></ColumnReference></Identifier></ScalarOperator></Convert></ScalarOperator><ScalarOperator><Identifier><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[Products]" Alias="[P]" Column="ItemId"></ColumnReference></Identifier></ScalarOperator></Compare></ScalarOperator></Predicate></IndexScan></RelOp></Sort></RelOp></NestedLoops></RelOp><RelOp NodeId="6" PhysicalOp="Index Spool" LogicalOp="Lazy Spool" EstimateRows="1" EstimateIO="0.003125" EstimateCPU="0.000258916" AvgRowSize="16" EstimatedTotalSubtreeCost="0.0887514" Parallel="0" EstimateRebinds="6.37984" EstimateRewinds="0.269846" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[ProductPrices]" Alias="[PP2]" Column="Value"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="7" Batches="0" ActualExecutionMode="Row" ActualElapsedms="15" ActualCPUms="15" ActualScans="14" ActualLogicalReads="53" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualRowsRead="7" ActualRebinds="7" ActualRewinds="0" ActualEndOfScans="0" ActualExecutions="7"></RunTimeCountersPerThread></RunTimeInformation><Spool><SeekPredicateNew><SeekKeys><Prefix ScanType="EQ"><RangeColumns><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[Products]" Alias="[P]" Column="ItemId"></ColumnReference><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[Products]" Alias="[P]" Column="Color"></ColumnReference><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[ProductPrices]" Alias="[PP]" Column="Group"></ColumnReference></RangeColumns><RangeExpressions><ScalarOperator ScalarString="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b].[dbo].[Products].[ItemId] as [P].[ItemId]"><Identifier><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[Products]" Alias="[P]" Column="ItemId"></ColumnReference></Identifier></ScalarOperator><ScalarOperator ScalarString="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b].[dbo].[Products].[Color] as [P].[Color]"><Identifier><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[Products]" Alias="[P]" Column="Color"></ColumnReference></Identifier></ScalarOperator><ScalarOperator ScalarString="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b].[dbo].[ProductPrices].[Group] as [PP].[Group]"><Identifier><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[ProductPrices]" Alias="[PP]" Column="Group"></ColumnReference></Identifier></ScalarOperator></RangeExpressions></Prefix></SeekKeys></SeekPredicateNew><RelOp NodeId="7" PhysicalOp="Sort" LogicalOp="TopN Sort" EstimateRows="1" EstimateRowsWithoutRowGoal="1.66667" EstimateIO="0.0112613" EstimateCPU="0.000101936" AvgRowSize="20" EstimatedTotalSubtreeCost="0.0836453" Parallel="0" EstimateRebinds="6.02411" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[ProductPrices]" Alias="[PP2]" Column="Value"></ColumnReference><ColumnReference Column="Expr1003"></ColumnReference></OutputList><MemoryFractions Input="0.2" Output="0.2"></MemoryFractions><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="7" Batches="0" ActualExecutionMode="Row" ActualElapsedms="15" ActualCPUms="15" ActualScans="0" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualRebinds="7" ActualRewinds="0" ActualEndOfScans="0" ActualExecutions="7" InputMemoryGrant="112" OutputMemoryGrant="112" UsedMemoryGrant="16"></RunTimeCountersPerThread></RunTimeInformation><TopSort Distinct="0" Rows="1"><OrderBy><OrderByColumn Ascending="1"><ColumnReference Column="Expr1003"></ColumnReference></OrderByColumn></OrderBy><RelOp NodeId="8" PhysicalOp="Compute Scalar" LogicalOp="Compute Scalar" EstimateRows="1.66667" EstimateRowsWithoutRowGoal="1.66667" EstimateIO="0" EstimateCPU="1.66667e-07" AvgRowSize="20" EstimatedTotalSubtreeCost="0.00382911" Parallel="0" EstimateRebinds="6.02411" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[ProductPrices]" Alias="[PP2]" Column="Value"></ColumnReference><ColumnReference Column="Expr1003"></ColumnReference></OutputList><ComputeScalar><DefinedValues><DefinedValue><ColumnReference Column="Expr1003"></ColumnReference><ScalarOperator ScalarString="CASE WHEN [Expr1005]=[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b].[dbo].[Products].[Color] as [P].[Color] THEN (1) ELSE CASE WHEN [fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b].[dbo].[ProductPrices].[Color] as [PP2].[Color] IS NULL THEN (2) ELSE (3) END END"><IF><Condition><ScalarOperator><Compare CompareOp="EQ"><ScalarOperator><Identifier><ColumnReference Column="Expr1005"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Identifier><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[Products]" Alias="[P]" Column="Color"></ColumnReference></Identifier></ScalarOperator></Compare></ScalarOperator></Condition><Then><ScalarOperator><Const ConstValue="(1)"></Const></ScalarOperator></Then><Else><ScalarOperator><IF><Condition><ScalarOperator><Compare CompareOp="IS"><ScalarOperator><Identifier><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[ProductPrices]" Alias="[PP2]" Column="Color"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="NULL"></Const></ScalarOperator></Compare></ScalarOperator></Condition><Then><ScalarOperator><Const ConstValue="(2)"></Const></ScalarOperator></Then><Else><ScalarOperator><Const ConstValue="(3)"></Const></ScalarOperator></Else></IF></ScalarOperator></Else></IF></ScalarOperator></DefinedValue></DefinedValues><RelOp NodeId="9" PhysicalOp="Compute Scalar" LogicalOp="Compute Scalar" EstimateRows="1.66667" EstimateIO="0" EstimateCPU="5e-07" AvgRowSize="156" EstimatedTotalSubtreeCost="0.00379704" Parallel="0" EstimateRebinds="0" EstimateRewinds="6.02411" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[ProductPrices]" Alias="[PP2]" Column="Value"></ColumnReference><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[ProductPrices]" Alias="[PP2]" Column="Color"></ColumnReference><ColumnReference Column="Expr1005"></ColumnReference></OutputList><ComputeScalar><DefinedValues><DefinedValue><ColumnReference Column="Expr1005"></ColumnReference><ScalarOperator ScalarString="CONVERT_IMPLICIT(nvarchar(50),[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b].[dbo].[ProductPrices].[Color] as [PP2].[Color],0)"><Convert DataType="nvarchar" Length="100" Style="0" Implicit="1"><ScalarOperator><Identifier><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[ProductPrices]" Alias="[PP2]" Column="Color"></ColumnReference></Identifier></ScalarOperator></Convert></ScalarOperator></DefinedValue></DefinedValues><RelOp NodeId="10" PhysicalOp="Clustered Index Scan" LogicalOp="Clustered Index Scan" EstimateRows="1.66667" EstimatedRowsRead="5" EstimateIO="0.0032035" EstimateCPU="8.4e-05" AvgRowSize="79" EstimatedTotalSubtreeCost="0.00379352" TableCardinality="5" Parallel="0" EstimateRebinds="0" EstimateRewinds="6.02411" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[ProductPrices]" Alias="[PP2]" Column="Value"></ColumnReference><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[ProductPrices]" Alias="[PP2]" Column="Color"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="13" Batches="0" ActualExecutionMode="Row" ActualElapsedms="15" ActualCPUms="15" ActualScans="7" ActualLogicalReads="14" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualRowsRead="35" ActualEndOfScans="7" ActualExecutions="7"></RunTimeCountersPerThread></RunTimeInformation><IndexScan Ordered="0" ForcedIndex="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[ProductPrices]" Alias="[PP2]" Column="Value"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[ProductPrices]" Alias="[PP2]" Column="Color"></ColumnReference></DefinedValue></DefinedValues><Object Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[ProductPrices]" Index="[pk_product_prices]" Alias="[PP2]" IndexKind="Clustered" Storage="RowStore"></Object><Predicate><ScalarOperator ScalarString="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b].[dbo].[ProductPrices].[Group] as [PP2].[Group]=[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b].[dbo].[ProductPrices].[Group] as [PP].[Group] AND CONVERT_IMPLICIT(nvarchar(50),[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b].[dbo].[ProductPrices].[ItemId] as [PP2].[ItemId],0)=[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b].[dbo].[Products].[ItemId] as [P].[ItemId]"><Logical Operation="AND"><ScalarOperator><Compare CompareOp="EQ"><ScalarOperator><Identifier><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[ProductPrices]" Alias="[PP2]" Column="Group"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Identifier><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[ProductPrices]" Alias="[PP]" Column="Group"></ColumnReference></Identifier></ScalarOperator></Compare></ScalarOperator><ScalarOperator><Compare CompareOp="EQ"><ScalarOperator><Convert DataType="nvarchar" Length="100" Style="0" Implicit="1"><ScalarOperator><Identifier><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[ProductPrices]" Alias="[PP2]" Column="ItemId"></ColumnReference></Identifier></ScalarOperator></Convert></ScalarOperator><ScalarOperator><Identifier><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[Products]" Alias="[P]" Column="ItemId"></ColumnReference></Identifier></ScalarOperator></Compare></ScalarOperator></Logical></ScalarOperator></Predicate></IndexScan></RelOp></ComputeScalar></RelOp></ComputeScalar></RelOp></TopSort></RelOp></Spool></RelOp></NestedLoops></RelOp></QueryPlan></StmtSimple></Statements></Batch></BatchSequence></ShowPlanXML>


WITH
    Joined AS
    (
        SELECT
            P.ProductId,
            P.ItemId,
            PP.[Group],
            Color = ISNULL(PP.Color, P.Color),
            PP.[Value],
            rnk = 
                CASE
                    WHEN PP.Color = P.Color THEN 1
                    WHEN PP.Color IS NULL THEN 2
                    ELSE 3
                END
        FROM dbo.Products AS P
        JOIN dbo.ProductPrices AS PP
            ON PP.ItemId = P.ItemId
    ),
    Ranked AS
    (
        SELECT
            *,
            rn = 
                ROW_NUMBER() OVER (
                    PARTITION BY Joined.ItemId, Joined.[Group], Joined.Color
                    ORDER BY Joined.rnk ASC)
        FROM Joined
    )
SELECT
    R.ProductId,
    R.ItemId,
    R.[Group],
    R.Color,
    R.[Value]
FROM Ranked AS R
WHERE 
    R.rn = 1
ORDER BY 
    R.ProductId,
    R.ItemId,
    R.[Group] DESC;


ProductId
ItemId
Group
Color
Value


1
X
T
5N
7.0000

1
X
M
5N
6.0000

2
X
T
10N
5.0000

2
X
M
10N
6.0000

3
X
T
2N
5.0000

3
X
M
2N
9.0000

4
Y
M
12S
12.0000




Microsoft SQL Server 2005 XML Showplan


<ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.539" Build="15.0.4003.23"><BatchSequence><Batch><Statements><StmtSimple StatementText="WITH&#xa;    Joined AS&#xa;    (&#xa;        SELECT&#xa;            P.ProductId,&#xa;            P.ItemId,&#xa;            PP.[Group],&#xa;            Color = ISNULL(PP.Color, P.Color),&#xa;            PP.[Value],&#xa;            rnk = &#xa;                CASE&#xa;                    WHEN PP.Color = P.Color THEN 1&#xa;                    WHEN PP.Color IS NULL THEN 2&#xa;                    ELSE 3&#xa;                END&#xa;        FROM dbo.Products AS P&#xa;        JOIN dbo.ProductPrices AS PP&#xa;            ON PP.ItemId = P.ItemId&#xa;    ),&#xa;    Ranked AS&#xa;    (&#xa;        SELECT&#xa;            *,&#xa;            rn = &#xa;                ROW_NUMBER() OVER (&#xa;                    PARTITION BY Joined.ItemId, Joined.[Group], Joined.Color&#xa;                    ORDER BY Joined.rnk ASC)&#xa;        FROM Joined&#xa;    )&#xa;SELECT&#xa;    R.ProductId,&#xa;    R.ItemId,&#xa;    R.[Group],&#xa;    R.Color,&#xa;    R.[Value]&#xa;FROM Ranked AS R&#xa;WHERE &#xa;    R.rn = 1&#xa;ORDER BY &#xa;    R.ProductId,&#xa;    R.ItemId,&#xa;    R.[Group] DESC" StatementId="1" StatementCompId="1" StatementType="SELECT" RetrievedFromCache="true" StatementSubTreeCost="0.0297996" StatementEstRows="13" SecurityPolicyApplied="false" StatementOptmLevel="FULL" QueryHash="0x2AE97FFDF3A504F1" QueryPlanHash="0x4FC3EB13CC14E90D" 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 DegreeOfParallelism="0" MemoryGrant="1024" NonParallelPlanReason="NoParallelPlansInDesktopOrExpressEdition" CachedPlanSize="56" CompileTime="0" CompileCPU="0" CompileMemory="480"><MemoryGrantInfo SerialRequiredMemory="640" SerialDesiredMemory="704" RequiredMemory="640" DesiredMemory="704" RequestedMemory="1024" GrantWaitTime="0" MaxQueryMemory="769008" GrantedMemory="1024" MaxUsedMemory="24"></MemoryGrantInfo><OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="838808" EstimatedPagesCached="52425" EstimatedAvailableDegreeOfParallelism="1" MaxCompileMemory="2548112"></OptimizerHardwareDependentProperties><OptimizerStatsUsage><StatisticsInfo LastUpdate="2020-03-17T14:39:40.02" ModificationCount="0" SamplingPercent="100" Statistics="[IX dbo.ProductPrices ItemId, Group (Color)]" Table="[ProductPrices]" Schema="[dbo]" Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]"></StatisticsInfo><StatisticsInfo LastUpdate="2020-03-17T14:39:40.02" ModificationCount="0" SamplingPercent="100" Statistics="[_WA_Sys_00000002_24927208]" Table="[Products]" Schema="[dbo]" Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]"></StatisticsInfo></OptimizerStatsUsage><TraceFlags IsCompileTime="1"><TraceFlag Value="460" Scope="Global"></TraceFlag><TraceFlag Value="8017" Scope="Global"></TraceFlag></TraceFlags><TraceFlags IsCompileTime="0"><TraceFlag Value="460" Scope="Global"></TraceFlag><TraceFlag Value="8017" Scope="Global"></TraceFlag></TraceFlags><QueryTimeStats ElapsedTime="0" CpuTime="0"></QueryTimeStats><RelOp NodeId="0" PhysicalOp="Sort" LogicalOp="Sort" EstimateRows="13" EstimateIO="0.0112613" EstimateCPU="0.000175173" AvgRowSize="128" EstimatedTotalSubtreeCost="0.0297996" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[Products]" Alias="[P]" Column="ProductId"></ColumnReference><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[Products]" Alias="[P]" Column="ItemId"></ColumnReference><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[ProductPrices]" Alias="[PP]" Column="Group"></ColumnReference><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[ProductPrices]" Alias="[PP]" Column="Value"></ColumnReference><ColumnReference Column="Expr1002"></ColumnReference></OutputList><MemoryFractions Input="0.5" Output="1"></MemoryFractions><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="7" 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="704" OutputMemoryGrant="512" UsedMemoryGrant="16"></RunTimeCountersPerThread></RunTimeInformation><Sort Distinct="0"><OrderBy><OrderByColumn Ascending="1"><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[Products]" Alias="[P]" Column="ProductId"></ColumnReference></OrderByColumn><OrderByColumn Ascending="0"><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[ProductPrices]" Alias="[PP]" Column="Group"></ColumnReference></OrderByColumn></OrderBy><RelOp NodeId="1" PhysicalOp="Filter" LogicalOp="Filter" EstimateRows="13" EstimateIO="0" EstimateCPU="6.24e-06" AvgRowSize="128" EstimatedTotalSubtreeCost="0.0183632" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[Products]" Alias="[P]" Column="ProductId"></ColumnReference><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[Products]" Alias="[P]" Column="ItemId"></ColumnReference><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[ProductPrices]" Alias="[PP]" Column="Group"></ColumnReference><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[ProductPrices]" Alias="[PP]" Column="Value"></ColumnReference><ColumnReference Column="Expr1002"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="7" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><Filter StartupExpression="0"><RelOp NodeId="2" PhysicalOp="Sequence Project" LogicalOp="Compute Scalar" EstimateRows="13" EstimateIO="0" EstimateCPU="1.04e-06" AvgRowSize="136" EstimatedTotalSubtreeCost="0.0183569" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[Products]" Alias="[P]" Column="ProductId"></ColumnReference><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[Products]" Alias="[P]" Column="ItemId"></ColumnReference><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[ProductPrices]" Alias="[PP]" Column="Group"></ColumnReference><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[ProductPrices]" Alias="[PP]" Column="Value"></ColumnReference><ColumnReference Column="Expr1002"></ColumnReference><ColumnReference Column="Expr1004"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="13" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><SequenceProject><DefinedValues><DefinedValue><ColumnReference Column="Expr1004"></ColumnReference><ScalarOperator ScalarString="row_number"><Sequence FunctionName="row_number"></Sequence></ScalarOperator></DefinedValue></DefinedValues><RelOp NodeId="3" PhysicalOp="Segment" LogicalOp="Segment" EstimateRows="13" EstimateIO="0" EstimateCPU="2.6e-07" AvgRowSize="136" EstimatedTotalSubtreeCost="0.0183559" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[Products]" Alias="[P]" Column="ProductId"></ColumnReference><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[Products]" Alias="[P]" Column="ItemId"></ColumnReference><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[ProductPrices]" Alias="[PP]" Column="Group"></ColumnReference><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[ProductPrices]" Alias="[PP]" Column="Value"></ColumnReference><ColumnReference Column="Expr1002"></ColumnReference><ColumnReference Column="Expr1003"></ColumnReference><ColumnReference Column="Segment1008"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="13" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><Segment><GroupBy><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[Products]" Alias="[P]" Column="ItemId"></ColumnReference><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[ProductPrices]" Alias="[PP]" Column="Group"></ColumnReference><ColumnReference Column="Expr1002"></ColumnReference></GroupBy><SegmentColumn><ColumnReference Column="Segment1008"></ColumnReference></SegmentColumn><RelOp NodeId="4" PhysicalOp="Sort" LogicalOp="Sort" EstimateRows="13" EstimateIO="0.0112613" EstimateCPU="0.000175177" AvgRowSize="132" EstimatedTotalSubtreeCost="0.0183556" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[Products]" Alias="[P]" Column="ProductId"></ColumnReference><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[Products]" Alias="[P]" Column="ItemId"></ColumnReference><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[ProductPrices]" Alias="[PP]" Column="Group"></ColumnReference><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[ProductPrices]" Alias="[PP]" Column="Value"></ColumnReference><ColumnReference Column="Expr1002"></ColumnReference><ColumnReference Column="Expr1003"></ColumnReference></OutputList><MemoryFractions Input="1" Output="0.5"></MemoryFractions><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="13" 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="896" OutputMemoryGrant="320" UsedMemoryGrant="16"></RunTimeCountersPerThread></RunTimeInformation><Sort Distinct="0"><OrderBy><OrderByColumn Ascending="1"><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[Products]" Alias="[P]" Column="ItemId"></ColumnReference></OrderByColumn><OrderByColumn Ascending="1"><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[ProductPrices]" Alias="[PP]" Column="Group"></ColumnReference></OrderByColumn><OrderByColumn Ascending="1"><ColumnReference Column="Expr1002"></ColumnReference></OrderByColumn><OrderByColumn Ascending="1"><ColumnReference Column="Expr1003"></ColumnReference></OrderByColumn></OrderBy><RelOp NodeId="5" PhysicalOp="Compute Scalar" LogicalOp="Compute Scalar" EstimateRows="13" EstimateIO="0" EstimateCPU="1.3e-06" AvgRowSize="132" EstimatedTotalSubtreeCost="0.0069192" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[Products]" Alias="[P]" Column="ProductId"></ColumnReference><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[Products]" Alias="[P]" Column="ItemId"></ColumnReference><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[ProductPrices]" Alias="[PP]" Column="Group"></ColumnReference><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[ProductPrices]" Alias="[PP]" Column="Value"></ColumnReference><ColumnReference Column="Expr1002"></ColumnReference><ColumnReference Column="Expr1003"></ColumnReference></OutputList><ComputeScalar><DefinedValues><DefinedValue><ColumnReference Column="Expr1002"></ColumnReference><ScalarOperator ScalarString="isnull([fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b].[dbo].[ProductPrices].[Color] as [PP].[Color],[Expr1005])"><Intrinsic FunctionName="isnull"><ScalarOperator><Identifier><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[ProductPrices]" Alias="[PP]" Column="Color"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Identifier><ColumnReference Column="Expr1005"></ColumnReference></Identifier></ScalarOperator></Intrinsic></ScalarOperator></DefinedValue><DefinedValue><ColumnReference Column="Expr1003"></ColumnReference><ScalarOperator ScalarString="CASE WHEN [Expr1006]=[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b].[dbo].[Products].[Color] as [P].[Color] THEN (1) ELSE CASE WHEN [fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b].[dbo].[ProductPrices].[Color] as [PP].[Color] IS NULL THEN (2) ELSE (3) END END"><IF><Condition><ScalarOperator><Compare CompareOp="EQ"><ScalarOperator><Identifier><ColumnReference Column="Expr1006"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Identifier><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[Products]" Alias="[P]" Column="Color"></ColumnReference></Identifier></ScalarOperator></Compare></ScalarOperator></Condition><Then><ScalarOperator><Const ConstValue="(1)"></Const></ScalarOperator></Then><Else><ScalarOperator><IF><Condition><ScalarOperator><Compare CompareOp="IS"><ScalarOperator><Identifier><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[ProductPrices]" Alias="[PP]" Column="Color"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="NULL"></Const></ScalarOperator></Compare></ScalarOperator></Condition><Then><ScalarOperator><Const ConstValue="(2)"></Const></ScalarOperator></Then><Else><ScalarOperator><Const ConstValue="(3)"></Const></ScalarOperator></Else></IF></ScalarOperator></Else></IF></ScalarOperator></DefinedValue></DefinedValues><RelOp NodeId="6" PhysicalOp="Nested Loops" LogicalOp="Inner Join" EstimateRows="13" EstimateIO="0" EstimateCPU="8.36e-05" AvgRowSize="259" EstimatedTotalSubtreeCost="0.0069179" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[Products]" Alias="[P]" Column="ProductId"></ColumnReference><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[Products]" Alias="[P]" Column="ItemId"></ColumnReference><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[Products]" Alias="[P]" Column="Color"></ColumnReference><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[ProductPrices]" Alias="[PP]" Column="Group"></ColumnReference><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[ProductPrices]" Alias="[PP]" Column="Value"></ColumnReference><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[ProductPrices]" Alias="[PP]" Column="Color"></ColumnReference><ColumnReference Column="Expr1005"></ColumnReference><ColumnReference Column="Expr1006"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="13" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><NestedLoops Optimized="0"><Predicate><ScalarOperator ScalarString="[Expr1007]=[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b].[dbo].[Products].[ItemId] as [P].[ItemId]"><Compare CompareOp="EQ"><ScalarOperator><Identifier><ColumnReference Column="Expr1007"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Identifier><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[Products]" Alias="[P]" Column="ItemId"></ColumnReference></Identifier></ScalarOperator></Compare></ScalarOperator></Predicate><RelOp NodeId="7" PhysicalOp="Compute Scalar" LogicalOp="Compute Scalar" EstimateRows="4" EstimateIO="0" EstimateCPU="4e-07" AvgRowSize="164" EstimatedTotalSubtreeCost="0.0032868" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[Products]" Alias="[P]" Column="ProductId"></ColumnReference><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[Products]" Alias="[P]" Column="ItemId"></ColumnReference><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[Products]" Alias="[P]" Column="Color"></ColumnReference><ColumnReference Column="Expr1005"></ColumnReference></OutputList><ComputeScalar><DefinedValues><DefinedValue><ColumnReference Column="Expr1005"></ColumnReference><ScalarOperator ScalarString="CONVERT_IMPLICIT(varchar(50),[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b].[dbo].[Products].[Color] as [P].[Color],0)"><Convert DataType="varchar" Length="50" Style="0" Implicit="1"><ScalarOperator><Identifier><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[Products]" Alias="[P]" Column="Color"></ColumnReference></Identifier></ScalarOperator></Convert></ScalarOperator></DefinedValue></DefinedValues><RelOp NodeId="8" PhysicalOp="Clustered Index Scan" LogicalOp="Clustered Index Scan" EstimateRows="4" EstimatedRowsRead="4" EstimateIO="0.003125" EstimateCPU="0.0001614" AvgRowSize="137" EstimatedTotalSubtreeCost="0.0032864" TableCardinality="4" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[Products]" Alias="[P]" Column="ProductId"></ColumnReference><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[Products]" Alias="[P]" Column="ItemId"></ColumnReference><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[Products]" Alias="[P]" Column="Color"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="4" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualScans="1" ActualLogicalReads="2" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualRowsRead="4" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><IndexScan Ordered="0" ForcedIndex="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[Products]" Alias="[P]" Column="ProductId"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[Products]" Alias="[P]" Column="ItemId"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[Products]" Alias="[P]" Column="Color"></ColumnReference></DefinedValue></DefinedValues><Object Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[Products]" Index="[PK_table_4]" Alias="[P]" IndexKind="Clustered" Storage="RowStore"></Object></IndexScan></RelOp></ComputeScalar></RelOp><RelOp NodeId="9" PhysicalOp="Compute Scalar" LogicalOp="Compute Scalar" EstimateRows="5" EstimateIO="0" EstimateCPU="5e-07" AvgRowSize="108" EstimatedTotalSubtreeCost="0.0035415" Parallel="0" EstimateRebinds="0" EstimateRewinds="3" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[ProductPrices]" Alias="[PP]" Column="Group"></ColumnReference><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[ProductPrices]" Alias="[PP]" Column="Value"></ColumnReference><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[ProductPrices]" Alias="[PP]" Column="Color"></ColumnReference><ColumnReference Column="Expr1006"></ColumnReference><ColumnReference Column="Expr1007"></ColumnReference></OutputList><ComputeScalar><DefinedValues><DefinedValue><ColumnReference Column="Expr1006"></ColumnReference><ScalarOperator ScalarString="CONVERT_IMPLICIT(nvarchar(50),[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b].[dbo].[ProductPrices].[Color] as [PP].[Color],0)"><Convert DataType="nvarchar" Length="100" Style="0" Implicit="1"><ScalarOperator><Identifier><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[ProductPrices]" Alias="[PP]" Column="Color"></ColumnReference></Identifier></ScalarOperator></Convert></ScalarOperator></DefinedValue><DefinedValue><ColumnReference Column="Expr1007"></ColumnReference><ScalarOperator ScalarString="CONVERT_IMPLICIT(nvarchar(50),[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b].[dbo].[ProductPrices].[ItemId] as [PP].[ItemId],0)"><Convert DataType="nvarchar" Length="100" Style="0" Implicit="1"><ScalarOperator><Identifier><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[ProductPrices]" Alias="[PP]" Column="ItemId"></ColumnReference></Identifier></ScalarOperator></Convert></ScalarOperator></DefinedValue></DefinedValues><RelOp NodeId="10" PhysicalOp="Clustered Index Scan" LogicalOp="Clustered Index Scan" EstimateRows="5" EstimatedRowsRead="5" EstimateIO="0.0032035" EstimateCPU="8.4e-05" AvgRowSize="55" EstimatedTotalSubtreeCost="0.0035395" TableCardinality="5" Parallel="0" EstimateRebinds="0" EstimateRewinds="3" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[ProductPrices]" Alias="[PP]" Column="ItemId"></ColumnReference><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[ProductPrices]" Alias="[PP]" Column="Group"></ColumnReference><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[ProductPrices]" Alias="[PP]" Column="Value"></ColumnReference><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[ProductPrices]" Alias="[PP]" Column="Color"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="20" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualScans="1" ActualLogicalReads="9" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualRowsRead="20" ActualEndOfScans="4" ActualExecutions="4"></RunTimeCountersPerThread></RunTimeInformation><IndexScan Ordered="0" ForcedIndex="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[ProductPrices]" Alias="[PP]" Column="ItemId"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[ProductPrices]" Alias="[PP]" Column="Group"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[ProductPrices]" Alias="[PP]" Column="Value"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[ProductPrices]" Alias="[PP]" Column="Color"></ColumnReference></DefinedValue></DefinedValues><Object Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[ProductPrices]" Index="[pk_product_prices]" Alias="[PP]" IndexKind="Clustered" Storage="RowStore"></Object></IndexScan></RelOp></ComputeScalar></RelOp></NestedLoops></RelOp></ComputeScalar></RelOp></Sort></RelOp></Segment></RelOp></SequenceProject></RelOp><Predicate><ScalarOperator ScalarString="[Expr1004]=(1)"><Compare CompareOp="EQ"><ScalarOperator><Identifier><ColumnReference Column="Expr1004"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="(1)"></Const></ScalarOperator></Compare></ScalarOperator></Predicate></Filter></RelOp></Sort></RelOp></QueryPlan></StmtSimple></Statements></Batch></BatchSequence></ShowPlanXML>


SELECT
    Q1.ProductId,
    Q1.ItemId,
    Q1.[Group],
    Q1.Color,
    Q1.[Value]
FROM 
(
    SELECT
        P.ProductId,
        P.ItemId,
        PP.[Group],
        Color = ISNULL(PP.Color, P.Color),
        PP.Value,
        rn = ROW_NUMBER() OVER (
            PARTITION BY P.ItemId, PP.[Group], ISNULL(PP.Color, P.Color)
            ORDER BY
                CASE
                    WHEN PP.Color = P.Color THEN 1
                    WHEN PP.Color IS NULL THEN 2
                    ELSE 3
                END)
    FROM dbo.Products AS P
    JOIN dbo.ProductPrices AS PP
        ON PP.ItemId = P.ItemId
) AS Q1
WHERE
    Q1.rn = 1
ORDER BY
    Q1.ProductId,
    Q1.ItemId,
    Q1.[Group] DESC;


ProductId
ItemId
Group
Color
Value


1
X
T
5N
7.0000

1
X
M
5N
6.0000

2
X
T
10N
5.0000

2
X
M
10N
6.0000

3
X
T
2N
5.0000

3
X
M
2N
9.0000

4
Y
M
12S
12.0000




Microsoft SQL Server 2005 XML Showplan


<ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.539" Build="15.0.4003.23"><BatchSequence><Batch><Statements><StmtSimple StatementText="SELECT&#xa;    Q1.ProductId,&#xa;    Q1.ItemId,&#xa;    Q1.[Group],&#xa;    Q1.Color,&#xa;    Q1.[Value]&#xa;FROM &#xa;(&#xa;    SELECT&#xa;        P.ProductId,&#xa;        P.ItemId,&#xa;        PP.[Group],&#xa;        Color = ISNULL(PP.Color, P.Color),&#xa;        PP.Value,&#xa;        rn = ROW_NUMBER() OVER (&#xa;            PARTITION BY P.ItemId, PP.[Group], ISNULL(PP.Color, P.Color)&#xa;            ORDER BY&#xa;                CASE&#xa;                    WHEN PP.Color = P.Color THEN 1&#xa;                    WHEN PP.Color IS NULL THEN 2&#xa;                    ELSE 3&#xa;                END)&#xa;    FROM dbo.Products AS P&#xa;    JOIN dbo.ProductPrices AS PP&#xa;        ON PP.ItemId = P.ItemId&#xa;) AS Q1&#xa;WHERE&#xa;    Q1.rn = 1&#xa;ORDER BY&#xa;    Q1.ProductId,&#xa;    Q1.ItemId,&#xa;    Q1.[Group] DESC" StatementId="1" StatementCompId="1" StatementType="SELECT" RetrievedFromCache="true" StatementSubTreeCost="0.0298023" StatementEstRows="13" SecurityPolicyApplied="false" StatementOptmLevel="FULL" QueryHash="0x84007D11D0348198" QueryPlanHash="0x51854ACBD991FFAA" 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 DegreeOfParallelism="0" MemoryGrant="1024" NonParallelPlanReason="NoParallelPlansInDesktopOrExpressEdition" CachedPlanSize="64" CompileTime="0" CompileCPU="0" CompileMemory="440"><MemoryGrantInfo SerialRequiredMemory="640" SerialDesiredMemory="704" RequiredMemory="640" DesiredMemory="704" RequestedMemory="1024" GrantWaitTime="0" MaxQueryMemory="769008" GrantedMemory="1024" MaxUsedMemory="24"></MemoryGrantInfo><OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="838808" EstimatedPagesCached="52425" EstimatedAvailableDegreeOfParallelism="1" MaxCompileMemory="2548112"></OptimizerHardwareDependentProperties><OptimizerStatsUsage><StatisticsInfo LastUpdate="2020-03-17T14:39:40.02" ModificationCount="0" SamplingPercent="100" Statistics="[IX dbo.ProductPrices ItemId, Group (Color)]" Table="[ProductPrices]" Schema="[dbo]" Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]"></StatisticsInfo><StatisticsInfo LastUpdate="2020-03-17T14:39:40.02" ModificationCount="0" SamplingPercent="100" Statistics="[_WA_Sys_00000002_24927208]" Table="[Products]" Schema="[dbo]" Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]"></StatisticsInfo></OptimizerStatsUsage><TraceFlags IsCompileTime="1"><TraceFlag Value="460" Scope="Global"></TraceFlag><TraceFlag Value="8017" Scope="Global"></TraceFlag></TraceFlags><TraceFlags IsCompileTime="0"><TraceFlag Value="460" Scope="Global"></TraceFlag><TraceFlag Value="8017" Scope="Global"></TraceFlag></TraceFlags><QueryTimeStats ElapsedTime="0" CpuTime="0"></QueryTimeStats><RelOp NodeId="0" PhysicalOp="Sort" LogicalOp="Sort" EstimateRows="13" EstimateIO="0.0112613" EstimateCPU="0.000175173" AvgRowSize="128" EstimatedTotalSubtreeCost="0.0298023" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[Products]" Alias="[P]" Column="ProductId"></ColumnReference><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[Products]" Alias="[P]" Column="ItemId"></ColumnReference><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[ProductPrices]" Alias="[PP]" Column="Group"></ColumnReference><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[ProductPrices]" Alias="[PP]" Column="Value"></ColumnReference><ColumnReference Column="Expr1005"></ColumnReference></OutputList><MemoryFractions Input="0.5" Output="1"></MemoryFractions><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="7" 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="704" OutputMemoryGrant="512" UsedMemoryGrant="16"></RunTimeCountersPerThread></RunTimeInformation><Sort Distinct="0"><OrderBy><OrderByColumn Ascending="1"><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[Products]" Alias="[P]" Column="ProductId"></ColumnReference></OrderByColumn><OrderByColumn Ascending="0"><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[ProductPrices]" Alias="[PP]" Column="Group"></ColumnReference></OrderByColumn></OrderBy><RelOp NodeId="1" PhysicalOp="Compute Scalar" LogicalOp="Compute Scalar" EstimateRows="13" EstimateIO="0" EstimateCPU="1.3e-06" AvgRowSize="128" EstimatedTotalSubtreeCost="0.0183659" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[Products]" Alias="[P]" Column="ProductId"></ColumnReference><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[Products]" Alias="[P]" Column="ItemId"></ColumnReference><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[ProductPrices]" Alias="[PP]" Column="Group"></ColumnReference><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[ProductPrices]" Alias="[PP]" Column="Value"></ColumnReference><ColumnReference Column="Expr1005"></ColumnReference></OutputList><ComputeScalar><DefinedValues><DefinedValue><ColumnReference Column="Expr1005"></ColumnReference><ScalarOperator ScalarString="isnull([fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b].[dbo].[ProductPrices].[Color] as [PP].[Color],[Expr1006])"><Intrinsic FunctionName="isnull"><ScalarOperator><Identifier><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[ProductPrices]" Alias="[PP]" Column="Color"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Identifier><ColumnReference Column="Expr1006"></ColumnReference></Identifier></ScalarOperator></Intrinsic></ScalarOperator></DefinedValue></DefinedValues><RelOp NodeId="2" PhysicalOp="Filter" LogicalOp="Filter" EstimateRows="13" EstimateIO="0" EstimateCPU="6.24e-06" AvgRowSize="155" EstimatedTotalSubtreeCost="0.0183646" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[Products]" Alias="[P]" Column="ProductId"></ColumnReference><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[Products]" Alias="[P]" Column="ItemId"></ColumnReference><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[ProductPrices]" Alias="[PP]" Column="Group"></ColumnReference><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[ProductPrices]" Alias="[PP]" Column="Value"></ColumnReference><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[ProductPrices]" Alias="[PP]" Column="Color"></ColumnReference><ColumnReference Column="Expr1006"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="7" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><Filter StartupExpression="0"><RelOp NodeId="3" PhysicalOp="Compute Scalar" LogicalOp="Compute Scalar" EstimateRows="13" EstimateIO="0" EstimateCPU="1.3e-06" AvgRowSize="163" EstimatedTotalSubtreeCost="0.0183583" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[Products]" Alias="[P]" Column="ProductId"></ColumnReference><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[Products]" Alias="[P]" Column="ItemId"></ColumnReference><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[ProductPrices]" Alias="[PP]" Column="Group"></ColumnReference><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[ProductPrices]" Alias="[PP]" Column="Value"></ColumnReference><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[ProductPrices]" Alias="[PP]" Column="Color"></ColumnReference><ColumnReference Column="Expr1004"></ColumnReference><ColumnReference Column="Expr1006"></ColumnReference></OutputList><ComputeScalar><DefinedValues><DefinedValue><ColumnReference Column="Expr1006"></ColumnReference><ScalarOperator ScalarString="CONVERT_IMPLICIT(varchar(50),[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b].[dbo].[Products].[Color] as [P].[Color],0)"><Convert DataType="varchar" Length="50" Style="0" Implicit="1"><ScalarOperator><Identifier><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[Products]" Alias="[P]" Column="Color"></ColumnReference></Identifier></ScalarOperator></Convert></ScalarOperator></DefinedValue></DefinedValues><RelOp NodeId="4" PhysicalOp="Sequence Project" LogicalOp="Compute Scalar" EstimateRows="13" EstimateIO="0" EstimateCPU="1.04e-06" AvgRowSize="188" EstimatedTotalSubtreeCost="0.018357" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[Products]" Alias="[P]" Column="ProductId"></ColumnReference><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[Products]" Alias="[P]" Column="ItemId"></ColumnReference><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[Products]" Alias="[P]" Column="Color"></ColumnReference><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[ProductPrices]" Alias="[PP]" Column="Group"></ColumnReference><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[ProductPrices]" Alias="[PP]" Column="Value"></ColumnReference><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[ProductPrices]" Alias="[PP]" Column="Color"></ColumnReference><ColumnReference Column="Expr1004"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="13" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><SequenceProject><DefinedValues><DefinedValue><ColumnReference Column="Expr1004"></ColumnReference><ScalarOperator ScalarString="row_number"><Sequence FunctionName="row_number"></Sequence></ScalarOperator></DefinedValue></DefinedValues><RelOp NodeId="5" PhysicalOp="Segment" LogicalOp="Segment" EstimateRows="13" EstimateIO="0" EstimateCPU="2.6e-07" AvgRowSize="188" EstimatedTotalSubtreeCost="0.018356" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[Products]" Alias="[P]" Column="ProductId"></ColumnReference><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[Products]" Alias="[P]" Column="ItemId"></ColumnReference><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[Products]" Alias="[P]" Column="Color"></ColumnReference><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[ProductPrices]" Alias="[PP]" Column="Group"></ColumnReference><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[ProductPrices]" Alias="[PP]" Column="Value"></ColumnReference><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[ProductPrices]" Alias="[PP]" Column="Color"></ColumnReference><ColumnReference Column="Expr1002"></ColumnReference><ColumnReference Column="Expr1003"></ColumnReference><ColumnReference Column="Segment1010"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="13" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><Segment><GroupBy><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[Products]" Alias="[P]" Column="ItemId"></ColumnReference><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[ProductPrices]" Alias="[PP]" Column="Group"></ColumnReference><ColumnReference Column="Expr1002"></ColumnReference></GroupBy><SegmentColumn><ColumnReference Column="Segment1010"></ColumnReference></SegmentColumn><RelOp NodeId="6" PhysicalOp="Sort" LogicalOp="Sort" EstimateRows="13" EstimateIO="0.0112613" EstimateCPU="0.000175256" AvgRowSize="211" EstimatedTotalSubtreeCost="0.0183557" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[Products]" Alias="[P]" Column="ProductId"></ColumnReference><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[Products]" Alias="[P]" Column="ItemId"></ColumnReference><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[Products]" Alias="[P]" Column="Color"></ColumnReference><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[ProductPrices]" Alias="[PP]" Column="Group"></ColumnReference><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[ProductPrices]" Alias="[PP]" Column="Value"></ColumnReference><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[ProductPrices]" Alias="[PP]" Column="Color"></ColumnReference><ColumnReference Column="Expr1002"></ColumnReference><ColumnReference Column="Expr1003"></ColumnReference></OutputList><MemoryFractions Input="1" Output="0.5"></MemoryFractions><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="13" 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="896" OutputMemoryGrant="320" UsedMemoryGrant="16"></RunTimeCountersPerThread></RunTimeInformation><Sort Distinct="0"><OrderBy><OrderByColumn Ascending="1"><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[Products]" Alias="[P]" Column="ItemId"></ColumnReference></OrderByColumn><OrderByColumn Ascending="1"><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[ProductPrices]" Alias="[PP]" Column="Group"></ColumnReference></OrderByColumn><OrderByColumn Ascending="1"><ColumnReference Column="Expr1002"></ColumnReference></OrderByColumn><OrderByColumn Ascending="1"><ColumnReference Column="Expr1003"></ColumnReference></OrderByColumn></OrderBy><RelOp NodeId="7" PhysicalOp="Compute Scalar" LogicalOp="Compute Scalar" EstimateRows="13" EstimateIO="0" EstimateCPU="1.3e-06" AvgRowSize="211" EstimatedTotalSubtreeCost="0.0069192" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[Products]" Alias="[P]" Column="ProductId"></ColumnReference><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[Products]" Alias="[P]" Column="ItemId"></ColumnReference><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[Products]" Alias="[P]" Column="Color"></ColumnReference><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[ProductPrices]" Alias="[PP]" Column="Group"></ColumnReference><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[ProductPrices]" Alias="[PP]" Column="Value"></ColumnReference><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[ProductPrices]" Alias="[PP]" Column="Color"></ColumnReference><ColumnReference Column="Expr1002"></ColumnReference><ColumnReference Column="Expr1003"></ColumnReference></OutputList><ComputeScalar><DefinedValues><DefinedValue><ColumnReference Column="Expr1002"></ColumnReference><ScalarOperator ScalarString="isnull([fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b].[dbo].[ProductPrices].[Color] as [PP].[Color],[Expr1007])"><Intrinsic FunctionName="isnull"><ScalarOperator><Identifier><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[ProductPrices]" Alias="[PP]" Column="Color"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Identifier><ColumnReference Column="Expr1007"></ColumnReference></Identifier></ScalarOperator></Intrinsic></ScalarOperator></DefinedValue><DefinedValue><ColumnReference Column="Expr1003"></ColumnReference><ScalarOperator ScalarString="CASE WHEN [Expr1008]=[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b].[dbo].[Products].[Color] as [P].[Color] THEN (1) ELSE CASE WHEN [fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b].[dbo].[ProductPrices].[Color] as [PP].[Color] IS NULL THEN (2) ELSE (3) END END"><IF><Condition><ScalarOperator><Compare CompareOp="EQ"><ScalarOperator><Identifier><ColumnReference Column="Expr1008"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Identifier><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[Products]" Alias="[P]" Column="Color"></ColumnReference></Identifier></ScalarOperator></Compare></ScalarOperator></Condition><Then><ScalarOperator><Const ConstValue="(1)"></Const></ScalarOperator></Then><Else><ScalarOperator><IF><Condition><ScalarOperator><Compare CompareOp="IS"><ScalarOperator><Identifier><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[ProductPrices]" Alias="[PP]" Column="Color"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="NULL"></Const></ScalarOperator></Compare></ScalarOperator></Condition><Then><ScalarOperator><Const ConstValue="(2)"></Const></ScalarOperator></Then><Else><ScalarOperator><Const ConstValue="(3)"></Const></ScalarOperator></Else></IF></ScalarOperator></Else></IF></ScalarOperator></DefinedValue></DefinedValues><RelOp NodeId="8" PhysicalOp="Nested Loops" LogicalOp="Inner Join" EstimateRows="13" EstimateIO="0" EstimateCPU="8.36e-05" AvgRowSize="259" EstimatedTotalSubtreeCost="0.0069179" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[Products]" Alias="[P]" Column="ProductId"></ColumnReference><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[Products]" Alias="[P]" Column="ItemId"></ColumnReference><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[Products]" Alias="[P]" Column="Color"></ColumnReference><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[ProductPrices]" Alias="[PP]" Column="Group"></ColumnReference><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[ProductPrices]" Alias="[PP]" Column="Value"></ColumnReference><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[ProductPrices]" Alias="[PP]" Column="Color"></ColumnReference><ColumnReference Column="Expr1007"></ColumnReference><ColumnReference Column="Expr1008"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="13" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><NestedLoops Optimized="0"><Predicate><ScalarOperator ScalarString="[Expr1009]=[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b].[dbo].[Products].[ItemId] as [P].[ItemId]"><Compare CompareOp="EQ"><ScalarOperator><Identifier><ColumnReference Column="Expr1009"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Identifier><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[Products]" Alias="[P]" Column="ItemId"></ColumnReference></Identifier></ScalarOperator></Compare></ScalarOperator></Predicate><RelOp NodeId="9" PhysicalOp="Compute Scalar" LogicalOp="Compute Scalar" EstimateRows="4" EstimateIO="0" EstimateCPU="4e-07" AvgRowSize="164" EstimatedTotalSubtreeCost="0.0032868" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[Products]" Alias="[P]" Column="ProductId"></ColumnReference><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[Products]" Alias="[P]" Column="ItemId"></ColumnReference><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[Products]" Alias="[P]" Column="Color"></ColumnReference><ColumnReference Column="Expr1007"></ColumnReference></OutputList><ComputeScalar><DefinedValues><DefinedValue><ColumnReference Column="Expr1007"></ColumnReference><ScalarOperator ScalarString="CONVERT_IMPLICIT(varchar(50),[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b].[dbo].[Products].[Color] as [P].[Color],0)"><Convert DataType="varchar" Length="50" Style="0" Implicit="1"><ScalarOperator><Identifier><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[Products]" Alias="[P]" Column="Color"></ColumnReference></Identifier></ScalarOperator></Convert></ScalarOperator></DefinedValue></DefinedValues><RelOp NodeId="10" PhysicalOp="Clustered Index Scan" LogicalOp="Clustered Index Scan" EstimateRows="4" EstimatedRowsRead="4" EstimateIO="0.003125" EstimateCPU="0.0001614" AvgRowSize="137" EstimatedTotalSubtreeCost="0.0032864" TableCardinality="4" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[Products]" Alias="[P]" Column="ProductId"></ColumnReference><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[Products]" Alias="[P]" Column="ItemId"></ColumnReference><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[Products]" Alias="[P]" Column="Color"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="4" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualScans="1" ActualLogicalReads="2" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualRowsRead="4" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><IndexScan Ordered="0" ForcedIndex="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[Products]" Alias="[P]" Column="ProductId"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[Products]" Alias="[P]" Column="ItemId"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[Products]" Alias="[P]" Column="Color"></ColumnReference></DefinedValue></DefinedValues><Object Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[Products]" Index="[PK_table_4]" Alias="[P]" IndexKind="Clustered" Storage="RowStore"></Object></IndexScan></RelOp></ComputeScalar></RelOp><RelOp NodeId="11" PhysicalOp="Compute Scalar" LogicalOp="Compute Scalar" EstimateRows="5" EstimateIO="0" EstimateCPU="5e-07" AvgRowSize="108" EstimatedTotalSubtreeCost="0.0035415" Parallel="0" EstimateRebinds="0" EstimateRewinds="3" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[ProductPrices]" Alias="[PP]" Column="Group"></ColumnReference><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[ProductPrices]" Alias="[PP]" Column="Value"></ColumnReference><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[ProductPrices]" Alias="[PP]" Column="Color"></ColumnReference><ColumnReference Column="Expr1008"></ColumnReference><ColumnReference Column="Expr1009"></ColumnReference></OutputList><ComputeScalar><DefinedValues><DefinedValue><ColumnReference Column="Expr1008"></ColumnReference><ScalarOperator ScalarString="CONVERT_IMPLICIT(nvarchar(50),[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b].[dbo].[ProductPrices].[Color] as [PP].[Color],0)"><Convert DataType="nvarchar" Length="100" Style="0" Implicit="1"><ScalarOperator><Identifier><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[ProductPrices]" Alias="[PP]" Column="Color"></ColumnReference></Identifier></ScalarOperator></Convert></ScalarOperator></DefinedValue><DefinedValue><ColumnReference Column="Expr1009"></ColumnReference><ScalarOperator ScalarString="CONVERT_IMPLICIT(nvarchar(50),[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b].[dbo].[ProductPrices].[ItemId] as [PP].[ItemId],0)"><Convert DataType="nvarchar" Length="100" Style="0" Implicit="1"><ScalarOperator><Identifier><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[ProductPrices]" Alias="[PP]" Column="ItemId"></ColumnReference></Identifier></ScalarOperator></Convert></ScalarOperator></DefinedValue></DefinedValues><RelOp NodeId="12" PhysicalOp="Clustered Index Scan" LogicalOp="Clustered Index Scan" EstimateRows="5" EstimatedRowsRead="5" EstimateIO="0.0032035" EstimateCPU="8.4e-05" AvgRowSize="55" EstimatedTotalSubtreeCost="0.0035395" TableCardinality="5" Parallel="0" EstimateRebinds="0" EstimateRewinds="3" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[ProductPrices]" Alias="[PP]" Column="ItemId"></ColumnReference><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[ProductPrices]" Alias="[PP]" Column="Group"></ColumnReference><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[ProductPrices]" Alias="[PP]" Column="Value"></ColumnReference><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[ProductPrices]" Alias="[PP]" Column="Color"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="20" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualScans="1" ActualLogicalReads="9" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualRowsRead="20" ActualEndOfScans="4" ActualExecutions="4"></RunTimeCountersPerThread></RunTimeInformation><IndexScan Ordered="0" ForcedIndex="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[ProductPrices]" Alias="[PP]" Column="ItemId"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[ProductPrices]" Alias="[PP]" Column="Group"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[ProductPrices]" Alias="[PP]" Column="Value"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[ProductPrices]" Alias="[PP]" Column="Color"></ColumnReference></DefinedValue></DefinedValues><Object Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[ProductPrices]" Index="[pk_product_prices]" Alias="[PP]" IndexKind="Clustered" Storage="RowStore"></Object></IndexScan></RelOp></ComputeScalar></RelOp></NestedLoops></RelOp></ComputeScalar></RelOp></Sort></RelOp></Segment></RelOp></SequenceProject></RelOp></ComputeScalar></RelOp><Predicate><ScalarOperator ScalarString="[Expr1004]=(1)"><Compare CompareOp="EQ"><ScalarOperator><Identifier><ColumnReference Column="Expr1004"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="(1)"></Const></ScalarOperator></Compare></ScalarOperator></Predicate></Filter></RelOp></ComputeScalar></RelOp></Sort></RelOp></QueryPlan></StmtSimple></Statements></Batch></BatchSequence></ShowPlanXML>


SELECT
    P.ProductId,
    P.ItemId,
    G.[Group],
    P.Color,
    [Value] =
        IIF
        (
            EXISTS
            (
                SELECT
                    1
                FROM dbo.ProductPrices AS PP
                WHERE
                    PP.ItemId = CONVERT(varchar(50), P.ItemId)
                    AND PP.[Group] = G.[Group]
                    AND PP.Color = CONVERT(varchar(50), P.Color)
            ),
            (
                SELECT TOP (1)
                    PP.[Value]
                FROM dbo.ProductPrices AS PP
                WHERE
                    PP.ItemId = CONVERT(varchar(50), P.ItemId)
                    AND PP.[Group] = G.[Group]
                    AND PP.Color = CONVERT(varchar(50), P.Color)
            ),
            (
                SELECT TOP (1)
                    PP.[Value]
                FROM dbo.ProductPrices AS PP
                WHERE
                    PP.ItemId = CONVERT(varchar(50), P.ItemId)
                    AND PP.[Group] = G.[Group]
                    AND PP.Color IS NULL
            )
        )
FROM dbo.Products AS P
CROSS APPLY 
(
    SELECT DISTINCT TOP (9223372036854775807)
        PP.ItemId,
        PP.[Group]
    FROM dbo.ProductPrices AS PP
    WHERE
        PP.ItemId = CONVERT(varchar(50), P.ItemId)
) AS G
ORDER BY 
    P.ProductId,
    P.ItemId,
    G.[Group] DESC;


ProductId
ItemId
Group
Color
Value


1
X
T
5N
7.0000

1
X
M
5N
6.0000

2
X
T
10N
5.0000

2
X
M
10N
6.0000

3
X
T
2N
5.0000

3
X
M
2N
9.0000

4
Y
M
12S
12.0000




Microsoft SQL Server 2005 XML Showplan


<ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.539" Build="15.0.4003.23"><BatchSequence><Batch><Statements><StmtSimple StatementText="SELECT&#xd;&#xa;    P.ProductId,&#xd;&#xa;    P.ItemId,&#xd;&#xa;    G.[Group],&#xd;&#xa;    P.Color,&#xd;&#xa;    [Value] =&#xd;&#xa;        IIF&#xd;&#xa;        (&#xd;&#xa;            EXISTS&#xd;&#xa;            (&#xd;&#xa;                SELECT&#xd;&#xa;                    1&#xd;&#xa;                FROM dbo.ProductPrices AS PP&#xd;&#xa;                WHERE&#xd;&#xa;                    PP.ItemId = CONVERT(varchar(50), P.ItemId)&#xd;&#xa;                    AND PP.[Group] = G.[Group]&#xd;&#xa;                    AND PP.Color = CONVERT(varchar(50), P.Color)&#xd;&#xa;            ),&#xd;&#xa;            (&#xd;&#xa;                SELECT TOP (1)&#xd;&#xa;                    PP.[Value]&#xd;&#xa;                FROM dbo.ProductPrices AS PP&#xd;&#xa;                WHERE&#xd;&#xa;                    PP.ItemId = CONVERT(varchar(50), P.ItemId)&#xd;&#xa;                    AND PP.[Group] = G.[Group]&#xd;&#xa;                    AND PP.Color = CONVERT(varchar(50), P.Color)&#xd;&#xa;            ),&#xd;&#xa;            (&#xd;&#xa;                SELECT TOP (1)&#xd;&#xa;                    PP.[Value]&#xd;&#xa;                FROM dbo.ProductPrices AS PP&#xd;&#xa;                WHERE&#xd;&#xa;                    PP.ItemId = CONVERT(varchar(50), P.ItemId)&#xd;&#xa;                    AND PP.[Group] = G.[Group]&#xd;&#xa;                    AND PP.Color IS NULL&#xd;&#xa;            )&#xd;&#xa;        )&#xd;&#xa;FROM dbo.Products AS P&#xd;&#xa;CROSS APPLY &#xd;&#xa;(&#xd;&#xa;    SELECT DISTINCT TOP (9223372036854775807)&#xd;&#xa;        PP.ItemId,&#xd;&#xa;        PP.[Group]&#xd;&#xa;    FROM dbo.ProductPrices AS PP&#xd;&#xa;    WHERE&#xd;&#xa;        PP.ItemId = CONVERT(varchar(50), P.ItemId)&#xd;&#xa;) AS G&#xd;&#xa;ORDER BY &#xd;&#xa;    P.ProductId,&#xd;&#xa;    P.ItemId,&#xd;&#xa;    G.[Group] DESC" StatementId="1" StatementCompId="1" StatementType="SELECT" RetrievedFromCache="true" StatementSubTreeCost="0.0191146" StatementEstRows="1" SecurityPolicyApplied="false" StatementOptmLevel="FULL" QueryHash="0x0D907B84048E45E7" QueryPlanHash="0x1ECEF187BC237112" 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 DegreeOfParallelism="0" NonParallelPlanReason="NoParallelPlansInDesktopOrExpressEdition" CachedPlanSize="64" CompileTime="15" CompileCPU="0" CompileMemory="568"><MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0" GrantedMemory="0" MaxUsedMemory="0"></MemoryGrantInfo><OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="838808" EstimatedPagesCached="52425" EstimatedAvailableDegreeOfParallelism="1" MaxCompileMemory="2548112"></OptimizerHardwareDependentProperties><OptimizerStatsUsage><StatisticsInfo LastUpdate="2020-03-17T14:39:40.04" ModificationCount="0" SamplingPercent="100" Statistics="[_WA_Sys_00000005_22AA2996]" Table="[ProductPrices]" Schema="[dbo]" Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]"></StatisticsInfo><StatisticsInfo LastUpdate="2020-03-17T14:39:40.02" ModificationCount="0" SamplingPercent="100" Statistics="[IX dbo.ProductPrices ItemId, Group (Color)]" Table="[ProductPrices]" Schema="[dbo]" Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]"></StatisticsInfo><StatisticsInfo LastUpdate="2020-03-17T14:39:40.02" ModificationCount="0" SamplingPercent="100" Statistics="[_WA_Sys_00000002_24927208]" Table="[Products]" Schema="[dbo]" Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]"></StatisticsInfo><StatisticsInfo LastUpdate="2020-03-17T14:39:40.02" ModificationCount="0" SamplingPercent="100" Statistics="[_WA_Sys_00000003_22AA2996]" Table="[ProductPrices]" Schema="[dbo]" Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]"></StatisticsInfo><StatisticsInfo LastUpdate="2020-03-17T14:39:40.02" ModificationCount="0" SamplingPercent="100" Statistics="[_WA_Sys_00000003_24927208]" Table="[Products]" Schema="[dbo]" Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]"></StatisticsInfo></OptimizerStatsUsage><TraceFlags IsCompileTime="1"><TraceFlag Value="460" Scope="Global"></TraceFlag><TraceFlag Value="8017" Scope="Global"></TraceFlag></TraceFlags><TraceFlags IsCompileTime="0"><TraceFlag Value="460" Scope="Global"></TraceFlag><TraceFlag Value="8017" Scope="Global"></TraceFlag></TraceFlags><QueryTimeStats ElapsedTime="0" CpuTime="0"></QueryTimeStats><RelOp NodeId="0" PhysicalOp="Compute Scalar" LogicalOp="Compute Scalar" EstimateRows="1" EstimateIO="0" EstimateCPU="1e-07" AvgRowSize="104" EstimatedTotalSubtreeCost="0.0191146" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[Products]" Alias="[P]" Column="ProductId"></ColumnReference><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[Products]" Alias="[P]" Column="ItemId"></ColumnReference><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[Products]" Alias="[P]" Column="Color"></ColumnReference><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[ProductPrices]" Alias="[PP]" Column="Group"></ColumnReference><ColumnReference Column="Expr1008"></ColumnReference></OutputList><ComputeScalar><DefinedValues><DefinedValue><ColumnReference Column="Expr1008"></ColumnReference><ScalarOperator ScalarString="CASE WHEN [Expr1009] THEN [fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b].[dbo].[ProductPrices].[Value] as [PP].[Value] ELSE [fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b].[dbo].[ProductPrices].[Value] as [PP].[Value] END"><IF><Condition><ScalarOperator><Identifier><ColumnReference Column="Expr1009"></ColumnReference></Identifier></ScalarOperator></Condition><Then><ScalarOperator><Identifier><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[ProductPrices]" Alias="[PP]" Column="Value"></ColumnReference></Identifier></ScalarOperator></Then><Else><ScalarOperator><Identifier><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[ProductPrices]" Alias="[PP]" Column="Value"></ColumnReference></Identifier></ScalarOperator></Else></IF></ScalarOperator></DefinedValue></DefinedValues><RelOp NodeId="1" PhysicalOp="Nested Loops" LogicalOp="Left Outer Join" EstimateRows="1" EstimateIO="0" EstimateCPU="4.18e-06" AvgRowSize="114" EstimatedTotalSubtreeCost="0.0191145" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[Products]" Alias="[P]" Column="ProductId"></ColumnReference><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[Products]" Alias="[P]" Column="ItemId"></ColumnReference><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[Products]" Alias="[P]" Column="Color"></ColumnReference><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[ProductPrices]" Alias="[PP]" Column="Group"></ColumnReference><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[ProductPrices]" Alias="[PP]" Column="Value"></ColumnReference><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[ProductPrices]" Alias="[PP]" Column="Value"></ColumnReference><ColumnReference Column="Expr1009"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="7" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><NestedLoops Optimized="0"><PassThru><ScalarOperator ScalarString="[Expr1009]"><Identifier><ColumnReference Column="Expr1009"></ColumnReference></Identifier></ScalarOperator></PassThru><OuterReferences><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[Products]" Alias="[P]" Column="ItemId"></ColumnReference><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[ProductPrices]" Alias="[PP]" Column="Group"></ColumnReference></OuterReferences><RelOp NodeId="2" PhysicalOp="Nested Loops" LogicalOp="Left Outer Join" EstimateRows="1" EstimateIO="0" EstimateCPU="3.19757e-05" AvgRowSize="105" EstimatedTotalSubtreeCost="0.015827" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[Products]" Alias="[P]" Column="ProductId"></ColumnReference><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[Products]" Alias="[P]" Column="ItemId"></ColumnReference><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[Products]" Alias="[P]" Column="Color"></ColumnReference><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[ProductPrices]" Alias="[PP]" Column="Group"></ColumnReference><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[ProductPrices]" Alias="[PP]" Column="Value"></ColumnReference><ColumnReference Column="Expr1009"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="7" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><NestedLoops Optimized="0"><PassThru><ScalarOperator ScalarString="IsFalseOrNull [Expr1009]"><Logical Operation="IsFalseOrNull"><ScalarOperator><Identifier><ColumnReference Column="Expr1009"></ColumnReference></Identifier></ScalarOperator></Logical></ScalarOperator></PassThru><OuterReferences><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[Products]" Alias="[P]" Column="ItemId"></ColumnReference><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[Products]" Alias="[P]" Column="Color"></ColumnReference><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[ProductPrices]" Alias="[PP]" Column="Group"></ColumnReference></OuterReferences><RelOp NodeId="3" PhysicalOp="Nested Loops" LogicalOp="Left Semi Join" EstimateRows="7.64969" EstimateIO="0" EstimateCPU="3.19757e-05" AvgRowSize="96" EstimatedTotalSubtreeCost="0.0114584" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[Products]" Alias="[P]" Column="ProductId"></ColumnReference><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[Products]" Alias="[P]" Column="ItemId"></ColumnReference><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[Products]" Alias="[P]" Column="Color"></ColumnReference><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[ProductPrices]" Alias="[PP]" Column="Group"></ColumnReference><ColumnReference Column="Expr1009"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="7" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><NestedLoops Optimized="0"><DefinedValues><DefinedValue><ColumnReference Column="Expr1009"></ColumnReference></DefinedValue></DefinedValues><OuterReferences><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[Products]" Alias="[P]" Column="ItemId"></ColumnReference><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[Products]" Alias="[P]" Column="Color"></ColumnReference><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[ProductPrices]" Alias="[PP]" Column="Group"></ColumnReference></OuterReferences><ProbeColumn><ColumnReference Column="Expr1009"></ColumnReference></ProbeColumn><RelOp NodeId="4" PhysicalOp="Nested Loops" LogicalOp="Inner Join" EstimateRows="7.64969" EstimateIO="0" EstimateCPU="3.19757e-05" AvgRowSize="95" EstimatedTotalSubtreeCost="0.00709197" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[Products]" Alias="[P]" Column="ProductId"></ColumnReference><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[Products]" Alias="[P]" Column="ItemId"></ColumnReference><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[Products]" Alias="[P]" Column="Color"></ColumnReference><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[ProductPrices]" Alias="[PP]" Column="Group"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="7" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><NestedLoops Optimized="0"><OuterReferences><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[Products]" Alias="[P]" Column="ItemId"></ColumnReference></OuterReferences><RelOp NodeId="5" PhysicalOp="Clustered Index Scan" LogicalOp="Clustered Index Scan" EstimateRows="4" EstimatedRowsRead="4" EstimateIO="0.003125" EstimateCPU="0.0001614" AvgRowSize="92" EstimatedTotalSubtreeCost="0.0032864" TableCardinality="4" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[Products]" Alias="[P]" Column="ProductId"></ColumnReference><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[Products]" Alias="[P]" Column="ItemId"></ColumnReference><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[Products]" Alias="[P]" Column="Color"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="4" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualScans="1" ActualLogicalReads="2" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualRowsRead="4" 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_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[Products]" Alias="[P]" Column="ProductId"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[Products]" Alias="[P]" Column="ItemId"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[Products]" Alias="[P]" Column="Color"></ColumnReference></DefinedValue></DefinedValues><Object Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[Products]" Index="[PK_table_4]" Alias="[P]" IndexKind="Clustered" Storage="RowStore"></Object></IndexScan></RelOp><RelOp NodeId="6" PhysicalOp="Top" LogicalOp="Top" EstimateRows="1.91242" EstimateIO="0" EstimateCPU="1.91242e-07" AvgRowSize="12" EstimatedTotalSubtreeCost="0.00377359" Parallel="0" EstimateRebinds="1.5" EstimateRewinds="1.5" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[ProductPrices]" Alias="[PP]" Column="Group"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="7" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="4" ActualExecutions="4"></RunTimeCountersPerThread></RunTimeInformation><Top RowCount="0" IsPercent="0" WithTies="0"><TopExpression><ScalarOperator ScalarString="(9223372036854775807)"><Const ConstValue="(9223372036854775807)"></Const></ScalarOperator></TopExpression><RelOp NodeId="7" PhysicalOp="Stream Aggregate" LogicalOp="Aggregate" EstimateRows="1.91242" EstimateIO="0" EstimateCPU="2.20621e-06" AvgRowSize="12" EstimatedTotalSubtreeCost="0.00377282" Parallel="0" EstimateRebinds="1.5" EstimateRewinds="1.5" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[ProductPrices]" Alias="[PP]" Column="Group"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="7" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="4" ActualExecutions="4"></RunTimeCountersPerThread></RunTimeInformation><StreamAggregate><DefinedValues></DefinedValues><GroupBy><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[ProductPrices]" Alias="[PP]" Column="Group"></ColumnReference></GroupBy><RelOp NodeId="8" PhysicalOp="Index Seek" LogicalOp="Index Seek" EstimateRows="2.5" EstimatedRowsRead="2.5" EstimateIO="0.003125" EstimateCPU="0.00015975" AvgRowSize="12" EstimatedTotalSubtreeCost="0.003764" TableCardinality="5" Parallel="0" EstimateRebinds="1.5" EstimateRewinds="1.5" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[ProductPrices]" Alias="[PP]" Column="Group"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="13" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualScans="4" ActualLogicalReads="8" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualRowsRead="13" ActualEndOfScans="4" ActualExecutions="4"></RunTimeCountersPerThread></RunTimeInformation><IndexScan Ordered="1" ScanDirection="BACKWARD" ForcedIndex="0" ForceSeek="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[ProductPrices]" Alias="[PP]" Column="Group"></ColumnReference></DefinedValue></DefinedValues><Object Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[ProductPrices]" Index="[IX dbo.ProductPrices ItemId, Group (Color)]" Alias="[PP]" TableReferenceId="1" IndexKind="NonClustered" Storage="RowStore"></Object><SeekPredicates><SeekPredicateNew><SeekKeys><Prefix ScanType="EQ"><RangeColumns><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[ProductPrices]" Alias="[PP]" Column="ItemId"></ColumnReference></RangeColumns><RangeExpressions><ScalarOperator ScalarString="CONVERT(varchar(50),[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b].[dbo].[Products].[ItemId] as [P].[ItemId],0)"><Convert DataType="varchar" Length="50" Style="0" Implicit="0"><ScalarOperator><Identifier><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[Products]" Alias="[P]" Column="ItemId"></ColumnReference></Identifier></ScalarOperator></Convert></ScalarOperator></RangeExpressions></Prefix></SeekKeys></SeekPredicateNew></SeekPredicates></IndexScan></RelOp></StreamAggregate></RelOp></Top></RelOp></NestedLoops></RelOp><RelOp NodeId="9" PhysicalOp="Index Seek" LogicalOp="Index Seek" EstimateRows="1" EstimatedRowsRead="1" EstimateIO="0.003125" EstimateCPU="0.0001581" AvgRowSize="9" EstimatedTotalSubtreeCost="0.00433442" TableCardinality="5" Parallel="0" EstimateRebinds="6.37984" EstimateRewinds="0.269846" EstimatedExecutionMode="Row"><OutputList></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="2" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualScans="7" ActualLogicalReads="14" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualRowsRead="2" ActualEndOfScans="5" ActualExecutions="7"></RunTimeCountersPerThread></RunTimeInformation><IndexScan Ordered="1" ScanDirection="FORWARD" ForcedIndex="0" ForceSeek="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues></DefinedValues><Object Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[ProductPrices]" Index="[IX dbo.ProductPrices ItemId, Group (Color)]" Alias="[PP]" TableReferenceId="2" IndexKind="NonClustered" Storage="RowStore"></Object><SeekPredicates><SeekPredicateNew><SeekKeys><Prefix ScanType="EQ"><RangeColumns><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[ProductPrices]" Alias="[PP]" Column="ItemId"></ColumnReference><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[ProductPrices]" Alias="[PP]" Column="Group"></ColumnReference><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[ProductPrices]" Alias="[PP]" Column="Color"></ColumnReference></RangeColumns><RangeExpressions><ScalarOperator ScalarString="CONVERT(varchar(50),[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b].[dbo].[Products].[ItemId] as [P].[ItemId],0)"><Convert DataType="varchar" Length="50" Style="0" Implicit="0"><ScalarOperator><Identifier><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[Products]" Alias="[P]" Column="ItemId"></ColumnReference></Identifier></ScalarOperator></Convert></ScalarOperator><ScalarOperator ScalarString="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b].[dbo].[ProductPrices].[Group] as [PP].[Group]"><Identifier><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[ProductPrices]" Alias="[PP]" Column="Group"></ColumnReference></Identifier></ScalarOperator><ScalarOperator ScalarString="CONVERT(varchar(50),[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b].[dbo].[Products].[Color] as [P].[Color],0)"><Convert DataType="varchar" Length="50" Style="0" Implicit="0"><ScalarOperator><Identifier><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[Products]" Alias="[P]" Column="Color"></ColumnReference></Identifier></ScalarOperator></Convert></ScalarOperator></RangeExpressions></Prefix></SeekKeys></SeekPredicateNew></SeekPredicates></IndexScan></RelOp></NestedLoops></RelOp><RelOp NodeId="10" PhysicalOp="Top" LogicalOp="Top" EstimateRows="1" EstimateIO="0" EstimateCPU="1e-07" AvgRowSize="16" EstimatedTotalSubtreeCost="0.00433518" Parallel="0" EstimateRebinds="6.37984" EstimateRewinds="0.269846" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[ProductPrices]" Alias="[PP]" Column="Value"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="2" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="0" ActualExecutions="2"></RunTimeCountersPerThread></RunTimeInformation><Top RowCount="0" IsPercent="0" WithTies="0"><TopExpression><ScalarOperator ScalarString="(1)"><Const ConstValue="(1)"></Const></ScalarOperator></TopExpression><RelOp NodeId="11" PhysicalOp="Index Seek" LogicalOp="Index Seek" EstimateRows="1" EstimatedRowsRead="1" EstimateIO="0.003125" EstimateCPU="0.0001581" AvgRowSize="16" EstimatedTotalSubtreeCost="0.00433442" TableCardinality="5" Parallel="0" EstimateRebinds="6.37984" EstimateRewinds="0.269846" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[ProductPrices]" Alias="[PP]" Column="Value"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="2" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualScans="2" ActualLogicalReads="4" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualRowsRead="2" ActualEndOfScans="0" ActualExecutions="2"></RunTimeCountersPerThread></RunTimeInformation><IndexScan Ordered="1" ScanDirection="FORWARD" ForcedIndex="0" ForceSeek="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[ProductPrices]" Alias="[PP]" Column="Value"></ColumnReference></DefinedValue></DefinedValues><Object Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[ProductPrices]" Index="[IX dbo.ProductPrices ItemId, Group (Color)]" Alias="[PP]" TableReferenceId="3" IndexKind="NonClustered" Storage="RowStore"></Object><SeekPredicates><SeekPredicateNew><SeekKeys><Prefix ScanType="EQ"><RangeColumns><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[ProductPrices]" Alias="[PP]" Column="ItemId"></ColumnReference><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[ProductPrices]" Alias="[PP]" Column="Group"></ColumnReference><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[ProductPrices]" Alias="[PP]" Column="Color"></ColumnReference></RangeColumns><RangeExpressions><ScalarOperator ScalarString="CONVERT(varchar(50),[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b].[dbo].[Products].[ItemId] as [P].[ItemId],0)"><Convert DataType="varchar" Length="50" Style="0" Implicit="0"><ScalarOperator><Identifier><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[Products]" Alias="[P]" Column="ItemId"></ColumnReference></Identifier></ScalarOperator></Convert></ScalarOperator><ScalarOperator ScalarString="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b].[dbo].[ProductPrices].[Group] as [PP].[Group]"><Identifier><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[ProductPrices]" Alias="[PP]" Column="Group"></ColumnReference></Identifier></ScalarOperator><ScalarOperator ScalarString="CONVERT(varchar(50),[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b].[dbo].[Products].[Color] as [P].[Color],0)"><Convert DataType="varchar" Length="50" Style="0" Implicit="0"><ScalarOperator><Identifier><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[Products]" Alias="[P]" Column="Color"></ColumnReference></Identifier></ScalarOperator></Convert></ScalarOperator></RangeExpressions></Prefix></SeekKeys></SeekPredicateNew></SeekPredicates></IndexScan></RelOp></Top></RelOp></NestedLoops></RelOp><RelOp NodeId="12" PhysicalOp="Top" LogicalOp="Top" EstimateRows="1" EstimateIO="0" EstimateCPU="1e-07" AvgRowSize="16" EstimatedTotalSubtreeCost="0.0032832" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[ProductPrices]" Alias="[PP]" Column="Value"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="5" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="0" ActualExecutions="5"></RunTimeCountersPerThread></RunTimeInformation><Top RowCount="0" IsPercent="0" WithTies="0"><TopExpression><ScalarOperator ScalarString="(1)"><Const ConstValue="(1)"></Const></ScalarOperator></TopExpression><RelOp NodeId="13" PhysicalOp="Index Seek" LogicalOp="Index Seek" EstimateRows="1" EstimateRowsWithoutRowGoal="1" EstimatedRowsRead="1" EstimateIO="0.003125" EstimateCPU="0.0001581" AvgRowSize="16" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="5" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[ProductPrices]" Alias="[PP]" Column="Value"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="5" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualScans="5" ActualLogicalReads="10" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualRowsRead="5" ActualEndOfScans="0" ActualExecutions="5"></RunTimeCountersPerThread></RunTimeInformation><IndexScan Ordered="1" ScanDirection="FORWARD" ForcedIndex="0" ForceSeek="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[ProductPrices]" Alias="[PP]" Column="Value"></ColumnReference></DefinedValue></DefinedValues><Object Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[ProductPrices]" Index="[IX dbo.ProductPrices ItemId, Group (Color)]" Alias="[PP]" TableReferenceId="4" IndexKind="NonClustered" Storage="RowStore"></Object><SeekPredicates><SeekPredicateNew><SeekKeys><Prefix ScanType="EQ"><RangeColumns><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[ProductPrices]" Alias="[PP]" Column="ItemId"></ColumnReference><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[ProductPrices]" Alias="[PP]" Column="Group"></ColumnReference><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[ProductPrices]" Alias="[PP]" Column="Color"></ColumnReference></RangeColumns><RangeExpressions><ScalarOperator ScalarString="CONVERT(varchar(50),[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b].[dbo].[Products].[ItemId] as [P].[ItemId],0)"><Convert DataType="varchar" Length="50" Style="0" Implicit="0"><ScalarOperator><Identifier><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[Products]" Alias="[P]" Column="ItemId"></ColumnReference></Identifier></ScalarOperator></Convert></ScalarOperator><ScalarOperator ScalarString="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b].[dbo].[ProductPrices].[Group] as [PP].[Group]"><Identifier><ColumnReference Database="[fiddle_9b9adaf22bed4fc5b7c1c16498fdaa5b]" Schema="[dbo]" Table="[ProductPrices]" Alias="[PP]" Column="Group"></ColumnReference></Identifier></ScalarOperator><ScalarOperator ScalarString="NULL"><Const ConstValue="NULL"></Const></ScalarOperator></RangeExpressions></Prefix></SeekKeys></SeekPredicateNew></SeekPredicates></IndexScan></RelOp></Top></RelOp></NestedLoops></RelOp></ComputeScalar></RelOp></QueryPlan></StmtSimple></Statements></Batch></BatchSequence></ShowPlanXML>
ProductId	ItemId	Group	Color	Value
1	X	T	5N	7.0000
1	X	M	5N	6.0000
2	X	T	10N	5.0000
2	X	M	10N	6.0000
3	X	T	2N	5.0000
3	X	M	2N	9.0000
4	Y	M	12S	12.0000