SQL Server 2022

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE dbo.t4
(
    a   integer NULL,
    b   varchar(1000) NULL,
    p   varchar(100) NULL
);

CREATE TABLE dbo.t5
(
    a   integer NULL,
    b   varchar(1000) NULL
);

CREATE UNIQUE CLUSTERED INDEX c1 
ON dbo.t5 (a);
SET STATISTICS XML ON;
WITH cte AS (
    SELECT *
    FROM dbo.t5
    WHERE t5.a BETWEEN 10 and 20
)
SELECT 
    z.a
FROM dbo.t5 AS z
WHERE 
    EXISTS 
    (
        SELECT TOP ((SELECT 100)) PERCENT y.a 
        FROM cte AS y
        WHERE y.a = z.a
    )
  AND  
    EXISTS 
    (
        SELECT TOP ((SELECT 100)) PERCENT y.a 
        FROM cte AS y
        WHERE y.a = z.a
    );


a




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="WITH cte AS (&#xa;    SELECT *&#xa;    FROM dbo.t5&#xa;    WHERE t5.a BETWEEN 10 and 20&#xa;)&#xa;SELECT &#xa;    z.a&#xa;FROM dbo.t5 AS z&#xa;WHERE &#xa;    EXISTS &#xa;    (&#xa;        SELECT TOP ((SELECT 100)) PERCENT y.a &#xa;        FROM cte AS y&#xa;        WHERE y.a = z.a&#xa;    )&#xa;  AND  &#xa;    EXISTS &#xa;    (&#xa;        SELECT TOP ((SELECT 100)) PERCENT y.a &#xa;        FROM cte AS y&#xa;        WHERE y.a = z.a&#xa;    )" StatementId="1" StatementCompId="1" StatementType="SELECT" StatementSqlHandle="0x0900E807A29CBF59B98D8D1D40ABB73104570000000000000000000000000000000000000000000000000000" DatabaseContextSettingsId="1" ParentObjectId="0" StatementParameterizationType="0" RetrievedFromCache="true" StatementSubTreeCost="0.0100707" StatementEstRows="1" SecurityPolicyApplied="false" StatementOptmLevel="FULL" QueryHash="0xF724AE9CAF5A98C2" QueryPlanHash="0x7AA3FCD20A1B111E" 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 DegreeOfParallelism="0" NonParallelPlanReason="NoParallelPlansInDesktopOrExpressEdition" CachedPlanSize="40" CompileTime="187" CompileCPU="0" CompileMemory="440"><MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0" GrantedMemory="0" MaxUsedMemory="0"></MemoryGrantInfo><OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="1258238" EstimatedPagesCached="78639" EstimatedAvailableDegreeOfParallelism="1" MaxCompileMemory="1503688"></OptimizerHardwareDependentProperties><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="Nested Loops" LogicalOp="Left Semi Join" EstimateRows="1" EstimateIO="0" EstimateCPU="4.18e-06" AvgRowSize="11" EstimatedTotalSubtreeCost="0.0100707" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_5bcec57642544992ad32d4c34e541f97]" Schema="[dbo]" Table="[t5]" Alias="[z]" Column="a"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="0" Batches="0" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row"></RunTimeCountersPerThread></RunTimeInformation><NestedLoops Optimized="0"><OuterReferences><ColumnReference Database="[fiddle_5bcec57642544992ad32d4c34e541f97]" Schema="[dbo]" Table="[t5]" Alias="[z]" Column="a"></ColumnReference></OuterReferences><RelOp NodeId="1" PhysicalOp="Nested Loops" LogicalOp="Left Semi Join" EstimateRows="1" EstimateIO="0" EstimateCPU="4.18e-06" AvgRowSize="11" EstimatedTotalSubtreeCost="0.0066769" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_5bcec57642544992ad32d4c34e541f97]" Schema="[dbo]" Table="[t5]" Alias="[z]" Column="a"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="0" Batches="0" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row"></RunTimeCountersPerThread></RunTimeInformation><NestedLoops Optimized="0"><OuterReferences><ColumnReference Database="[fiddle_5bcec57642544992ad32d4c34e541f97]" Schema="[dbo]" Table="[t5]" Alias="[z]" Column="a"></ColumnReference></OuterReferences><RelOp NodeId="2" PhysicalOp="Clustered Index Scan" LogicalOp="Clustered Index Scan" EstimateRows="1" EstimatedRowsRead="1" EstimateIO="0.003125" EstimateCPU="0.0001581" AvgRowSize="11" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="0" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_5bcec57642544992ad32d4c34e541f97]" Schema="[dbo]" Table="[t5]" Alias="[z]" Column="a"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="0" Batches="0" ActualElapsedms="0" ActualCPUms="0" ActualScans="1" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row"></RunTimeCountersPerThread></RunTimeInformation><IndexScan Ordered="0" ForcedIndex="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Database="[fiddle_5bcec57642544992ad32d4c34e541f97]" Schema="[dbo]" Table="[t5]" Alias="[z]" Column="a"></ColumnReference></DefinedValue></DefinedValues><Object Database="[fiddle_5bcec57642544992ad32d4c34e541f97]" Schema="[dbo]" Table="[t5]" Index="[c1]" Alias="[z]" IndexKind="Clustered" Storage="RowStore"></Object></IndexScan></RelOp><RelOp NodeId="3" PhysicalOp="Nested Loops" LogicalOp="Inner Join" EstimateRows="1" EstimateIO="0" EstimateCPU="4.18e-06" AvgRowSize="9" EstimatedTotalSubtreeCost="0.00338962" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="0" Batches="0" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="0" ActualExecutions="0" ActualExecutionMode="Row"></RunTimeCountersPerThread></RunTimeInformation><NestedLoops Optimized="0"><OuterReferences><ColumnReference Column="Expr1007"></ColumnReference></OuterReferences><RelOp NodeId="4" PhysicalOp="Compute Scalar" LogicalOp="Compute Scalar" EstimateRows="1" EstimateIO="0" EstimateCPU="1e-07" AvgRowSize="15" EstimatedTotalSubtreeCost="1.257e-06" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Column="Expr1007"></ColumnReference></OutputList><ComputeScalar><DefinedValues><DefinedValue><ColumnReference Column="Expr1007"></ColumnReference><ScalarOperator ScalarString="CONVERT_IMPLICIT(float(53),[Expr1003],0)"><Convert DataType="float" Precision="53" Style="0" Implicit="1"><ScalarOperator><Identifier><ColumnReference Column="Expr1003"></ColumnReference></Identifier></ScalarOperator></Convert></ScalarOperator></DefinedValue></DefinedValues><RelOp NodeId="5" PhysicalOp="Constant Scan" LogicalOp="Constant Scan" EstimateRows="1" EstimateIO="0" EstimateCPU="1.157e-06" AvgRowSize="11" EstimatedTotalSubtreeCost="1.157e-06" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Column="Expr1003"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="0" Batches="0" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="0" ActualExecutions="0" ActualExecutionMode="Row"></RunTimeCountersPerThread></RunTimeInformation><ConstantScan><Values><Row><ScalarOperator ScalarString="(100)"><Const ConstValue="(100)"></Const></ScalarOperator></Row></Values></ConstantScan></RelOp></ComputeScalar></RelOp><RelOp NodeId="6" PhysicalOp="Top" LogicalOp="Top" EstimateRows="1" EstimateIO="0" EstimateCPU="1e-07" AvgRowSize="9" EstimatedTotalSubtreeCost="0.00338418" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="0" Batches="0" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="0" ActualExecutions="0" ActualExecutionMode="Row"></RunTimeCountersPerThread></RunTimeInformation><Top RowCount="0" IsPercent="1" WithTies="0"><TopExpression><ScalarOperator ScalarString="[Expr1007]"><Identifier><ColumnReference Column="Expr1007"></ColumnReference></Identifier></ScalarOperator></TopExpression><RelOp NodeId="7" PhysicalOp="Row Count Spool" LogicalOp="Eager Spool" EstimateRows="1" EstimateIO="0" EstimateCPU="0.0001001" AvgRowSize="9" EstimatedTotalSubtreeCost="0.00338408" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="0" Batches="0" ActualElapsedms="0" ActualCPUms="0" ActualRebinds="0" ActualRewinds="0" ActualEndOfScans="0" ActualExecutions="0" ActualExecutionMode="Row"></RunTimeCountersPerThread></RunTimeInformation><RowCountSpool><RelOp NodeId="8" PhysicalOp="Clustered Index Seek" LogicalOp="Clustered Index Seek" EstimateRows="1" EstimatedRowsRead="1" EstimateIO="0.003125" EstimateCPU="0.0001581" AvgRowSize="11" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="0" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="0" Batches="0" ActualElapsedms="0" ActualCPUms="0" ActualScans="0" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualEndOfScans="0" ActualExecutions="0" ActualExecutionMode="Row"></RunTimeCountersPerThread></RunTimeInformation><IndexScan Ordered="1" ScanDirection="FORWARD" ForcedIndex="0" ForceSeek="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues></DefinedValues><Object Database="[fiddle_5bcec57642544992ad32d4c34e541f97]" Schema="[dbo]" Table="[t5]" Index="[c1]" TableReferenceId="1" IndexKind="Clustered" Storage="RowStore"></Object><SeekPredicates><SeekPredicateNew><SeekKeys><Prefix ScanType="EQ"><RangeColumns><ColumnReference Database="[fiddle_5bcec57642544992ad32d4c34e541f97]" Schema="[dbo]" Table="[t5]" Column="a"></ColumnReference></RangeColumns><RangeExpressions><ScalarOperator ScalarString="[fiddle_5bcec57642544992ad32d4c34e541f97].[dbo].[t5].[a] as [z].[a]"><Identifier><ColumnReference Database="[fiddle_5bcec57642544992ad32d4c34e541f97]" Schema="[dbo]" Table="[t5]" Alias="[z]" Column="a"></ColumnReference></Identifier></ScalarOperator></RangeExpressions></Prefix></SeekKeys></SeekPredicateNew></SeekPredicates><Predicate><ScalarOperator ScalarString="[fiddle_5bcec57642544992ad32d4c34e541f97].[dbo].[t5].[a]&gt;=(10) AND [fiddle_5bcec57642544992ad32d4c34e541f97].[dbo].[t5].[a]&lt;=(20)"><Logical Operation="AND"><ScalarOperator><Compare CompareOp="GE"><ScalarOperator><Identifier><ColumnReference Database="[fiddle_5bcec57642544992ad32d4c34e541f97]" Schema="[dbo]" Table="[t5]" Column="a"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="(10)"></Const></ScalarOperator></Compare></ScalarOperator><ScalarOperator><Compare CompareOp="LE"><ScalarOperator><Identifier><ColumnReference Database="[fiddle_5bcec57642544992ad32d4c34e541f97]" Schema="[dbo]" Table="[t5]" Column="a"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="(20)"></Const></ScalarOperator></Compare></ScalarOperator></Logical></ScalarOperator></Predicate></IndexScan></RelOp></RowCountSpool></RelOp></Top></RelOp></NestedLoops></RelOp></NestedLoops></RelOp><RelOp NodeId="9" PhysicalOp="Nested Loops" LogicalOp="Inner Join" EstimateRows="1" EstimateIO="0" EstimateCPU="4.18e-06" AvgRowSize="9" EstimatedTotalSubtreeCost="0.00338962" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="0" Batches="0" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="0" ActualExecutions="0" ActualExecutionMode="Row"></RunTimeCountersPerThread></RunTimeInformation><NestedLoops Optimized="0"><OuterReferences><ColumnReference Column="Expr1008"></ColumnReference></OuterReferences><RelOp NodeId="10" PhysicalOp="Compute Scalar" LogicalOp="Compute Scalar" EstimateRows="1" EstimateIO="0" EstimateCPU="1e-07" AvgRowSize="15" EstimatedTotalSubtreeCost="1.257e-06" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Column="Expr1008"></ColumnReference></OutputList><ComputeScalar><DefinedValues><DefinedValue><ColumnReference Column="Expr1008"></ColumnReference><ScalarOperator ScalarString="CONVERT_IMPLICIT(float(53),[Expr1006],0)"><Convert DataType="float" Precision="53" Style="0" Implicit="1"><ScalarOperator><Identifier><ColumnReference Column="Expr1006"></ColumnReference></Identifier></ScalarOperator></Convert></ScalarOperator></DefinedValue></DefinedValues><RelOp NodeId="11" PhysicalOp="Constant Scan" LogicalOp="Constant Scan" EstimateRows="1" EstimateIO="0" EstimateCPU="1.157e-06" AvgRowSize="11" EstimatedTotalSubtreeCost="1.157e-06" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Column="Expr1006"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="0" Batches="0" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="0" ActualExecutions="0" ActualExecutionMode="Row"></RunTimeCountersPerThread></RunTimeInformation><ConstantScan><Values><Row><ScalarOperator ScalarString="(100)"><Const ConstValue="(100)"></Const></ScalarOperator></Row></Values></ConstantScan></RelOp></ComputeScalar></RelOp><RelOp NodeId="12" PhysicalOp="Top" LogicalOp="Top" EstimateRows="1" EstimateIO="0" EstimateCPU="1e-07" AvgRowSize="9" EstimatedTotalSubtreeCost="0.00338418" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="0" Batches="0" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="0" ActualExecutions="0" ActualExecutionMode="Row"></RunTimeCountersPerThread></RunTimeInformation><Top RowCount="0" IsPercent="1" WithTies="0"><TopExpression><ScalarOperator ScalarString="[Expr1008]"><Identifier><ColumnReference Column="Expr1008"></ColumnReference></Identifier></ScalarOperator></TopExpression><RelOp NodeId="13" PhysicalOp="Row Count Spool" LogicalOp="Eager Spool" EstimateRows="1" EstimateIO="0" EstimateCPU="0.0001001" AvgRowSize="9" EstimatedTotalSubtreeCost="0.00338408" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="0" Batches="0" ActualElapsedms="0" ActualCPUms="0" ActualRebinds="0" ActualRewinds="0" ActualEndOfScans="0" ActualExecutions="0" ActualExecutionMode="Row"></RunTimeCountersPerThread></RunTimeInformation><RowCountSpool><RelOp NodeId="14" PhysicalOp="Clustered Index Seek" LogicalOp="Clustered Index Seek" EstimateRows="1" EstimatedRowsRead="1" EstimateIO="0.003125" EstimateCPU="0.0001581" AvgRowSize="11" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="0" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="0" Batches="0" ActualElapsedms="0" ActualCPUms="0" ActualScans="0" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualEndOfScans="0" ActualExecutions="0" ActualExecutionMode="Row"></RunTimeCountersPerThread></RunTimeInformation><IndexScan Ordered="1" ScanDirection="FORWARD" ForcedIndex="0" ForceSeek="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues></DefinedValues><Object Database="[fiddle_5bcec57642544992ad32d4c34e541f97]" Schema="[dbo]" Table="[t5]" Index="[c1]" TableReferenceId="2" IndexKind="Clustered" Storage="RowStore"></Object><SeekPredicates><SeekPredicateNew><SeekKeys><Prefix ScanType="EQ"><RangeColumns><ColumnReference Database="[fiddle_5bcec57642544992ad32d4c34e541f97]" Schema="[dbo]" Table="[t5]" Column="a"></ColumnReference></RangeColumns><RangeExpressions><ScalarOperator ScalarString="[fiddle_5bcec57642544992ad32d4c34e541f97].[dbo].[t5].[a] as [z].[a]"><Identifier><ColumnReference Database="[fiddle_5bcec57642544992ad32d4c34e541f97]" Schema="[dbo]" Table="[t5]" Alias="[z]" Column="a"></ColumnReference></Identifier></ScalarOperator></RangeExpressions></Prefix></SeekKeys></SeekPredicateNew></SeekPredicates><Predicate><ScalarOperator ScalarString="[fiddle_5bcec57642544992ad32d4c34e541f97].[dbo].[t5].[a]&gt;=(10) AND [fiddle_5bcec57642544992ad32d4c34e541f97].[dbo].[t5].[a]&lt;=(20)"><Logical Operation="AND"><ScalarOperator><Compare CompareOp="GE"><ScalarOperator><Identifier><ColumnReference Database="[fiddle_5bcec57642544992ad32d4c34e541f97]" Schema="[dbo]" Table="[t5]" Column="a"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="(10)"></Const></ScalarOperator></Compare></ScalarOperator><ScalarOperator><Compare CompareOp="LE"><ScalarOperator><Identifier><ColumnReference Database="[fiddle_5bcec57642544992ad32d4c34e541f97]" Schema="[dbo]" Table="[t5]" Column="a"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="(20)"></Const></ScalarOperator></Compare></ScalarOperator></Logical></ScalarOperator></Predicate></IndexScan></RelOp></RowCountSpool></RelOp></Top></RelOp></NestedLoops></RelOp></NestedLoops></RelOp></QueryPlan></StmtSimple></Statements></Batch></BatchSequence></ShowPlanXML>