SQL Server 2022

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE dbo.Entries (
  UserId INT,
  Prize VARCHAR(100),
  INDEX CIX_Entries CLUSTERED (UserId, Prize),
  INDEX IX_Prize(Prize) INCLUDE(UserId)
);

INSERT dbo.Entries(UserId, Prize) 
VALUES 
(1, 'Gold'),
(1, 'Silver'),
(1, 'Bronze'),
(2, 'Bronze'),
(3, 'Gold'),
(4, 'Gold'),
(4, 'Gold');
7 rows affected

SELECT UserId
FROM dbo.Entries
WHERE Prize IN ('Silver', 'Gold')
GROUP BY UserId
HAVING COUNT(DISTINCT Prize) = 2;

SELECT UserId FROM dbo.Entries WHERE Prize = 'Gold'
INTERSECT
SELECT UserId FROM dbo.Entries WHERE Prize = 'Silver';


UserId


1




UserId


1


SET SHOWPLAN_XML ON;
SELECT UserId
FROM dbo.Entries
WHERE Prize IN ('Silver', 'Gold')
GROUP BY UserId
HAVING COUNT(DISTINCT Prize) = 2;


Microsoft SQL Server 2005 XML Showplan


<ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.564" Build="16.0.1000.6"><BatchSequence><Batch><Statements><StmtSimple StatementText="SELECT UserId&#xa;FROM dbo.Entries&#xa;WHERE Prize IN (&apos;Silver&apos;, &apos;Gold&apos;)&#xa;GROUP BY UserId&#xa;HAVING COUNT(DISTINCT Prize) = 2" StatementId="1" StatementCompId="1" StatementType="SELECT" RetrievedFromCache="false" StatementSubTreeCost="0.00330608" StatementEstRows="1.46325" SecurityPolicyApplied="false" StatementOptmLevel="FULL" QueryHash="0x81DA910CD064AC45" QueryPlanHash="0xA4BE7B5FCE9A45C1" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" CardinalityEstimationModelVersion="160"><StatementSetOptions QUOTED_IDENTIFIER="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" NUMERIC_ROUNDABORT="false"></StatementSetOptions><QueryPlan NonParallelPlanReason="NoParallelPlansInDesktopOrExpressEdition" CachedPlanSize="24" CompileTime="15" CompileCPU="15" CompileMemory="272"><MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0" GrantedMemory="0" MaxUsedMemory="0"></MemoryGrantInfo><OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="1258238" EstimatedPagesCached="78639" EstimatedAvailableDegreeOfParallelism="1" MaxCompileMemory="1444960"></OptimizerHardwareDependentProperties><OptimizerStatsUsage><StatisticsInfo LastUpdate="2024-02-21T14:51:06.45" ModificationCount="0" SamplingPercent="100" Statistics="[CIX_Entries]" Table="[Entries]" Schema="[dbo]" Database="[fiddle_4e587b382cfe45bba362c9da0287a88a]"></StatisticsInfo><StatisticsInfo LastUpdate="2024-02-21T14:51:06.45" ModificationCount="0" SamplingPercent="100" Statistics="[IX_Prize]" Table="[Entries]" Schema="[dbo]" Database="[fiddle_4e587b382cfe45bba362c9da0287a88a]"></StatisticsInfo></OptimizerStatsUsage><TraceFlags IsCompileTime="1"><TraceFlag Value="460" Scope="Global"></TraceFlag><TraceFlag Value="8017" Scope="Global"></TraceFlag></TraceFlags><RelOp NodeId="0" PhysicalOp="Filter" LogicalOp="Filter" EstimateRows="1.46325" EstimateIO="0" EstimateCPU="1.62389e-06" AvgRowSize="11" EstimatedTotalSubtreeCost="0.00330608" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_4e587b382cfe45bba362c9da0287a88a]" Schema="[dbo]" Table="[Entries]" Column="UserId"></ColumnReference></OutputList><Filter StartupExpression="0"><RelOp NodeId="1" PhysicalOp="Compute Scalar" LogicalOp="Compute Scalar" EstimateRows="3.3831" EstimateIO="0" EstimateCPU="0" AvgRowSize="15" EstimatedTotalSubtreeCost="0.00330445" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_4e587b382cfe45bba362c9da0287a88a]" Schema="[dbo]" Table="[Entries]" Column="UserId"></ColumnReference><ColumnReference Column="Expr1003"></ColumnReference></OutputList><ComputeScalar><DefinedValues><DefinedValue><ColumnReference Column="Expr1003"></ColumnReference><ScalarOperator ScalarString="CONVERT_IMPLICIT(int,[Expr1006],0)"><Convert DataType="int" Style="0" Implicit="1"><ScalarOperator><Identifier><ColumnReference Column="Expr1006"></ColumnReference></Identifier></ScalarOperator></Convert></ScalarOperator></DefinedValue></DefinedValues><RelOp NodeId="2" PhysicalOp="Stream Aggregate" LogicalOp="Aggregate" EstimateRows="3.3831" EstimateIO="0" EstimateCPU="4.09155e-06" AvgRowSize="15" EstimatedTotalSubtreeCost="0.00330445" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_4e587b382cfe45bba362c9da0287a88a]" Schema="[dbo]" Table="[Entries]" Column="UserId"></ColumnReference><ColumnReference Column="Expr1006"></ColumnReference></OutputList><StreamAggregate><DefinedValues><DefinedValue><ColumnReference Column="Expr1006"></ColumnReference><ScalarOperator ScalarString="Count(*)"><Aggregate AggType="countstar" Distinct="0"></Aggregate></ScalarOperator></DefinedValue></DefinedValues><GroupBy><ColumnReference Database="[fiddle_4e587b382cfe45bba362c9da0287a88a]" Schema="[dbo]" Table="[Entries]" Column="UserId"></ColumnReference></GroupBy><RelOp NodeId="3" PhysicalOp="Stream Aggregate" LogicalOp="Aggregate" EstimateRows="4" EstimateIO="0" EstimateCPU="4.5e-06" AvgRowSize="19" EstimatedTotalSubtreeCost="0.00330036" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_4e587b382cfe45bba362c9da0287a88a]" Schema="[dbo]" Table="[Entries]" Column="UserId"></ColumnReference><ColumnReference Database="[fiddle_4e587b382cfe45bba362c9da0287a88a]" Schema="[dbo]" Table="[Entries]" Column="Prize"></ColumnReference></OutputList><StreamAggregate><DefinedValues></DefinedValues><GroupBy><ColumnReference Database="[fiddle_4e587b382cfe45bba362c9da0287a88a]" Schema="[dbo]" Table="[Entries]" Column="UserId"></ColumnReference><ColumnReference Database="[fiddle_4e587b382cfe45bba362c9da0287a88a]" Schema="[dbo]" Table="[Entries]" Column="Prize"></ColumnReference></GroupBy><RelOp NodeId="4" PhysicalOp="Clustered Index Scan" LogicalOp="Clustered Index Scan" EstimateRows="5" EstimatedRowsRead="7" EstimateIO="0.003125" EstimateCPU="0.0001647" AvgRowSize="19" EstimatedTotalSubtreeCost="0.0032897" TableCardinality="7" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_4e587b382cfe45bba362c9da0287a88a]" Schema="[dbo]" Table="[Entries]" Column="UserId"></ColumnReference><ColumnReference Database="[fiddle_4e587b382cfe45bba362c9da0287a88a]" Schema="[dbo]" Table="[Entries]" Column="Prize"></ColumnReference></OutputList><IndexScan Ordered="1" ScanDirection="FORWARD" ForcedIndex="0" ForceSeek="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Database="[fiddle_4e587b382cfe45bba362c9da0287a88a]" Schema="[dbo]" Table="[Entries]" Column="UserId"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_4e587b382cfe45bba362c9da0287a88a]" Schema="[dbo]" Table="[Entries]" Column="Prize"></ColumnReference></DefinedValue></DefinedValues><Object Database="[fiddle_4e587b382cfe45bba362c9da0287a88a]" Schema="[dbo]" Table="[Entries]" Index="[CIX_Entries]" IndexKind="Clustered" Storage="RowStore"></Object><Predicate><ScalarOperator ScalarString="[fiddle_4e587b382cfe45bba362c9da0287a88a].[dbo].[Entries].[Prize]=&apos;Gold&apos; OR [fiddle_4e587b382cfe45bba362c9da0287a88a].[dbo].[Entries].[Prize]=&apos;Silver&apos;"><Logical Operation="OR"><ScalarOperator><Compare CompareOp="EQ"><ScalarOperator><Identifier><ColumnReference Database="[fiddle_4e587b382cfe45bba362c9da0287a88a]" Schema="[dbo]" Table="[Entries]" Column="Prize"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="&apos;Gold&apos;"></Const></ScalarOperator></Compare></ScalarOperator><ScalarOperator><Compare CompareOp="EQ"><ScalarOperator><Identifier><ColumnReference Database="[fiddle_4e587b382cfe45bba362c9da0287a88a]" Schema="[dbo]" Table="[Entries]" Column="Prize"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="&apos;Silver&apos;"></Const></ScalarOperator></Compare></ScalarOperator></Logical></ScalarOperator></Predicate></IndexScan></RelOp></StreamAggregate></RelOp></StreamAggregate></RelOp></ComputeScalar></RelOp><Predicate><ScalarOperator ScalarString="[Expr1003]=(2)"><Compare CompareOp="EQ"><ScalarOperator><Identifier><ColumnReference Column="Expr1003"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="(2)"></Const></ScalarOperator></Compare></ScalarOperator></Predicate></Filter></RelOp></QueryPlan></StmtSimple></Statements></Batch></BatchSequence></ShowPlanXML>


SELECT UserId FROM dbo.Entries WHERE Prize = 'Gold'
INTERSECT
SELECT UserId FROM dbo.Entries WHERE Prize = 'Silver';


Microsoft SQL Server 2005 XML Showplan


<ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.564" Build="16.0.1000.6"><BatchSequence><Batch><Statements><StmtSimple StatementText="SELECT UserId FROM dbo.Entries WHERE Prize = &apos;Gold&apos;&#xa;INTERSECT&#xa;SELECT UserId FROM dbo.Entries WHERE Prize = &apos;Silver&apos;" StatementId="1" StatementCompId="1" StatementType="SELECT" RetrievedFromCache="false" StatementSubTreeCost="0.0068961" StatementEstRows="1" SecurityPolicyApplied="false" StatementOptmLevel="FULL" QueryHash="0x51BCF398AF6C4358" QueryPlanHash="0x9AB3180D9CF1F15E" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" CardinalityEstimationModelVersion="160"><StatementSetOptions QUOTED_IDENTIFIER="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" NUMERIC_ROUNDABORT="false"></StatementSetOptions><QueryPlan NonParallelPlanReason="NoParallelPlansInDesktopOrExpressEdition" CachedPlanSize="24" CompileTime="0" CompileCPU="0" CompileMemory="336"><MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0" GrantedMemory="0" MaxUsedMemory="0"></MemoryGrantInfo><OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="1258238" EstimatedPagesCached="78639" EstimatedAvailableDegreeOfParallelism="1" MaxCompileMemory="1444960"></OptimizerHardwareDependentProperties><OptimizerStatsUsage><StatisticsInfo LastUpdate="2024-02-21T14:51:06.45" ModificationCount="0" SamplingPercent="100" Statistics="[CIX_Entries]" Table="[Entries]" Schema="[dbo]" Database="[fiddle_4e587b382cfe45bba362c9da0287a88a]"></StatisticsInfo><StatisticsInfo LastUpdate="2024-02-21T14:51:06.45" ModificationCount="0" SamplingPercent="100" Statistics="[IX_Prize]" Table="[Entries]" Schema="[dbo]" Database="[fiddle_4e587b382cfe45bba362c9da0287a88a]"></StatisticsInfo></OptimizerStatsUsage><TraceFlags IsCompileTime="1"><TraceFlag Value="460" Scope="Global"></TraceFlag><TraceFlag Value="8017" Scope="Global"></TraceFlag></TraceFlags><RelOp NodeId="0" PhysicalOp="Nested Loops" LogicalOp="Left Semi Join" EstimateRows="1" EstimateIO="0" EstimateCPU="1.23951e-05" AvgRowSize="11" EstimatedTotalSubtreeCost="0.0068961" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_4e587b382cfe45bba362c9da0287a88a]" Schema="[dbo]" Table="[Entries]" Column="UserId"></ColumnReference></OutputList><NestedLoops Optimized="0"><OuterReferences><ColumnReference Database="[fiddle_4e587b382cfe45bba362c9da0287a88a]" Schema="[dbo]" Table="[Entries]" Column="UserId"></ColumnReference></OuterReferences><RelOp NodeId="1" PhysicalOp="Stream Aggregate" LogicalOp="Aggregate" EstimateRows="2.96533" EstimateIO="0" EstimateCPU="3.48267e-06" AvgRowSize="11" EstimatedTotalSubtreeCost="0.00328988" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_4e587b382cfe45bba362c9da0287a88a]" Schema="[dbo]" Table="[Entries]" Column="UserId"></ColumnReference></OutputList><StreamAggregate><DefinedValues></DefinedValues><GroupBy><ColumnReference Database="[fiddle_4e587b382cfe45bba362c9da0287a88a]" Schema="[dbo]" Table="[Entries]" Column="UserId"></ColumnReference></GroupBy><RelOp NodeId="2" PhysicalOp="Index Seek" LogicalOp="Index Seek" EstimateRows="4" EstimatedRowsRead="4" EstimateIO="0.003125" EstimateCPU="0.0001614" AvgRowSize="11" EstimatedTotalSubtreeCost="0.0032864" TableCardinality="7" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_4e587b382cfe45bba362c9da0287a88a]" Schema="[dbo]" Table="[Entries]" Column="UserId"></ColumnReference></OutputList><IndexScan Ordered="1" ScanDirection="FORWARD" ForcedIndex="0" ForceSeek="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Database="[fiddle_4e587b382cfe45bba362c9da0287a88a]" Schema="[dbo]" Table="[Entries]" Column="UserId"></ColumnReference></DefinedValue></DefinedValues><Object Database="[fiddle_4e587b382cfe45bba362c9da0287a88a]" Schema="[dbo]" Table="[Entries]" Index="[IX_Prize]" TableReferenceId="1" IndexKind="NonClustered" Storage="RowStore"></Object><SeekPredicates><SeekPredicateNew><SeekKeys><Prefix ScanType="EQ"><RangeColumns><ColumnReference Database="[fiddle_4e587b382cfe45bba362c9da0287a88a]" Schema="[dbo]" Table="[Entries]" Column="Prize"></ColumnReference></RangeColumns><RangeExpressions><ScalarOperator ScalarString="&apos;Gold&apos;"><Const ConstValue="&apos;Gold&apos;"></Const></ScalarOperator></RangeExpressions></Prefix></SeekKeys></SeekPredicateNew></SeekPredicates></IndexScan></RelOp></StreamAggregate></RelOp><RelOp NodeId="3" PhysicalOp="Index Seek" LogicalOp="Index Seek" EstimateRows="1" EstimatedRowsRead="1" EstimateIO="0.003125" EstimateCPU="0.0001581" AvgRowSize="9" EstimatedTotalSubtreeCost="0.00359382" TableCardinality="7" Parallel="0" EstimateRebinds="1.96533" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList></OutputList><IndexScan Ordered="1" ScanDirection="FORWARD" ForcedIndex="0" ForceSeek="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues></DefinedValues><Object Database="[fiddle_4e587b382cfe45bba362c9da0287a88a]" Schema="[dbo]" Table="[Entries]" Index="[IX_Prize]" TableReferenceId="2" IndexKind="NonClustered" Storage="RowStore"></Object><SeekPredicates><SeekPredicateNew><SeekKeys><Prefix ScanType="EQ"><RangeColumns><ColumnReference Database="[fiddle_4e587b382cfe45bba362c9da0287a88a]" Schema="[dbo]" Table="[Entries]" Column="Prize"></ColumnReference><ColumnReference Database="[fiddle_4e587b382cfe45bba362c9da0287a88a]" Schema="[dbo]" Table="[Entries]" Column="UserId"></ColumnReference></RangeColumns><RangeExpressions><ScalarOperator ScalarString="&apos;Silver&apos;"><Const ConstValue="&apos;Silver&apos;"></Const></ScalarOperator><ScalarOperator ScalarString="[fiddle_4e587b382cfe45bba362c9da0287a88a].[dbo].[Entries].[UserId]"><Identifier><ColumnReference Database="[fiddle_4e587b382cfe45bba362c9da0287a88a]" Schema="[dbo]" Table="[Entries]" Column="UserId"></ColumnReference></Identifier></ScalarOperator></RangeExpressions></Prefix></SeekKeys></SeekPredicateNew></SeekPredicates></IndexScan></RelOp></NestedLoops></RelOp></QueryPlan></StmtSimple></Statements></Batch></BatchSequence></ShowPlanXML>