SQL Server 2016

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table t1 (id int primary key); insert into t1 values (1),(2),(3);
create table t2 (id int primary key); insert into t2 values (1),(4),(5);
6 rows affected

set statistics io, xml on;
select id 
from (
  select id
  from t1
  union all
  select id
  from t2
  ) t
group by id 
having count(1) =1

id

2

3

4

5

Microsoft SQL Server 2005 XML Showplan

<ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.5" Build="13.0.4001.0"><BatchSequence><Batch><Statements><StmtSimple StatementText="select id &#xa;from (&#xa;  select id&#xa;  from t1&#xa;  union all&#xa;  select id&#xa;  from t2&#xa;  ) t&#xa;group by id &#xa;having count(1) =1" StatementId="1" StatementCompId="2" StatementType="SELECT" RetrievedFromCache="true" StatementSubTreeCost="0.0121913" StatementEstRows="2.25" SecurityPolicyApplied="false" StatementOptmLevel="FULL" QueryHash="0x71114D691CEAB41D" QueryPlanHash="0x7E1045F93F30C0D" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" CardinalityEstimationModelVersion="130"><StatementSetOptions QUOTED_IDENTIFIER="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" NUMERIC_ROUNDABORT="false"></StatementSetOptions><QueryPlan DegreeOfParallelism="0" NonParallelPlanReason="NoParallelPlansInDesktopOrExpressEdition" CachedPlanSize="24" CompileTime="0" CompileCPU="0" CompileMemory="208"><MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0"></MemoryGrantInfo><OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="419378" EstimatedPagesCached="26211" EstimatedAvailableDegreeOfParallelism="1" MaxCompileMemory="1007104"></OptimizerHardwareDependentProperties><TraceFlags IsCompileTime="1"><TraceFlag Value="8017" Scope="Global"></TraceFlag></TraceFlags><TraceFlags IsCompileTime="0"><TraceFlag Value="8017" Scope="Global"></TraceFlag></TraceFlags><QueryTimeStats ElapsedTime="0" CpuTime="0"></QueryTimeStats><RelOp NodeId="0" PhysicalOp="Filter" LogicalOp="Filter" EstimateRows="2.25" EstimateIO="0" EstimateCPU="1.92e-006" AvgRowSize="11" EstimatedTotalSubtreeCost="0.0121913" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Column="Union1004"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="4" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><Filter StartupExpression="0"><RelOp NodeId="1" PhysicalOp="Compute Scalar" LogicalOp="Compute Scalar" EstimateRows="4" EstimateIO="0" EstimateCPU="0" AvgRowSize="15" EstimatedTotalSubtreeCost="0.0121894" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Column="Union1004"></ColumnReference><ColumnReference Column="Expr1005"></ColumnReference></OutputList><ComputeScalar><DefinedValues><DefinedValue><ColumnReference Column="Expr1005"></ColumnReference><ScalarOperator ScalarString="CONVERT_IMPLICIT(int,[Expr1008],0)"><Convert DataType="int" Style="0" Implicit="1"><ScalarOperator><Identifier><ColumnReference Column="Expr1008"></ColumnReference></Identifier></ScalarOperator></Convert></ScalarOperator></DefinedValue></DefinedValues><RelOp NodeId="2" PhysicalOp="Stream Aggregate" LogicalOp="Aggregate" EstimateRows="4" EstimateIO="0" EstimateCPU="5.6e-006" AvgRowSize="15" EstimatedTotalSubtreeCost="0.0121894" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Column="Union1004"></ColumnReference><ColumnReference Column="Expr1008"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="5" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><StreamAggregate><DefinedValues><DefinedValue><ColumnReference Column="Expr1008"></ColumnReference><ScalarOperator ScalarString="Count(*)"><Aggregate AggType="countstar" Distinct="0"></Aggregate></ScalarOperator></DefinedValue></DefinedValues><GroupBy><ColumnReference Column="Union1004"></ColumnReference></GroupBy><RelOp NodeId="3" PhysicalOp="Merge Join" LogicalOp="Concatenation" EstimateRows="6" EstimateIO="0" EstimateCPU="0.0056132" AvgRowSize="11" EstimatedTotalSubtreeCost="0.0121838" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Column="Union1004"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="6" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualScans="0" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><Merge><DefinedValues><DefinedValue><ColumnReference Column="Union1004"></ColumnReference><ColumnReference Database="[fiddle_0e375bcfb20b465d91e42ee832c7bc45]" Schema="[dbo]" Table="[t1]" Column="id"></ColumnReference><ColumnReference Database="[fiddle_0e375bcfb20b465d91e42ee832c7bc45]" Schema="[dbo]" Table="[t2]" Column="id"></ColumnReference></DefinedValue></DefinedValues><RelOp NodeId="4" PhysicalOp="Clustered Index Scan" LogicalOp="Clustered Index Scan" EstimateRows="3" EstimatedRowsRead="3" EstimateIO="0.003125" EstimateCPU="0.0001603" AvgRowSize="11" EstimatedTotalSubtreeCost="0.0032853" TableCardinality="3" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_0e375bcfb20b465d91e42ee832c7bc45]" Schema="[dbo]" Table="[t1]" Column="id"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="3" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualScans="1" ActualLogicalReads="2" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualRowsRead="3" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><IndexScan Ordered="1" ScanDirection="FORWARD" ForcedIndex="0" ForceSeek="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Database="[fiddle_0e375bcfb20b465d91e42ee832c7bc45]" Schema="[dbo]" Table="[t1]" Column="id"></ColumnReference></DefinedValue></DefinedValues><Object Database="[fiddle_0e375bcfb20b465d91e42ee832c7bc45]" Schema="[dbo]" Table="[t1]" Index="[PK__t1__3213E83FDF4FB5D1]" IndexKind="Clustered" Storage="RowStore"></Object></IndexScan></RelOp><RelOp NodeId="5" PhysicalOp="Clustered Index Scan" LogicalOp="Clustered Index Scan" EstimateRows="3" EstimatedRowsRead="3" EstimateIO="0.003125" EstimateCPU="0.0001603" AvgRowSize="11" EstimatedTotalSubtreeCost="0.0032853" TableCardinality="3" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_0e375bcfb20b465d91e42ee832c7bc45]" Schema="[dbo]" Table="[t2]" Column="id"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="3" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualScans="1" ActualLogicalReads="2" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualRowsRead="3" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><IndexScan Ordered="1" ScanDirection="FORWARD" ForcedIndex="0" ForceSeek="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Database="[fiddle_0e375bcfb20b465d91e42ee832c7bc45]" Schema="[dbo]" Table="[t2]" Column="id"></ColumnReference></DefinedValue></DefinedValues><Object Database="[fiddle_0e375bcfb20b465d91e42ee832c7bc45]" Schema="[dbo]" Table="[t2]" Index="[PK__t2__3213E83FA20DD0DE]" IndexKind="Clustered" Storage="RowStore"></Object></IndexScan></RelOp></Merge></RelOp></StreamAggregate></RelOp></ComputeScalar></RelOp><Predicate><ScalarOperator ScalarString="[Expr1005]=(1)"><Compare CompareOp="EQ"><ScalarOperator><Identifier><ColumnReference Column="Expr1005"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="(1)"></Const></ScalarOperator></Compare></ScalarOperator></Predicate></Filter></RelOp></QueryPlan></StmtSimple></Statements></Batch></BatchSequence></ShowPlanXML>

set statistics io, xml on;
select id = isnull(t1.id,t2.id)
from t1 
  full outer join t2 
    on t1.id = t2.id
where t1.id is null 
  or t2.id is null

id

2

3

4

5

Microsoft SQL Server 2005 XML Showplan

<ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.5" Build="13.0.4001.0"><BatchSequence><Batch><Statements><StmtSimple StatementText="select id = isnull(t1.id,t2.id)&#xa;from t1 &#xa;  full outer join t2 &#xa;    on t1.id = t2.id&#xa;where t1.id is null &#xa;  or t2.id is null" StatementId="1" StatementCompId="2" StatementType="SELECT" RetrievedFromCache="true" StatementSubTreeCost="0.0121915" StatementEstRows="4.03775" SecurityPolicyApplied="false" StatementOptmLevel="FULL" QueryHash="0x9C95DE239E572541" QueryPlanHash="0xCE782233CF1522C3" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" CardinalityEstimationModelVersion="130"><StatementSetOptions QUOTED_IDENTIFIER="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" NUMERIC_ROUNDABORT="false"></StatementSetOptions><QueryPlan DegreeOfParallelism="0" NonParallelPlanReason="NoParallelPlansInDesktopOrExpressEdition" CachedPlanSize="24" CompileTime="0" CompileCPU="0" CompileMemory="240"><MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0"></MemoryGrantInfo><OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="419378" EstimatedPagesCached="26211" EstimatedAvailableDegreeOfParallelism="1" MaxCompileMemory="1007104"></OptimizerHardwareDependentProperties><TraceFlags IsCompileTime="1"><TraceFlag Value="8017" Scope="Global"></TraceFlag></TraceFlags><TraceFlags IsCompileTime="0"><TraceFlag Value="8017" Scope="Global"></TraceFlag></TraceFlags><QueryTimeStats ElapsedTime="0" CpuTime="0"></QueryTimeStats><RelOp NodeId="0" PhysicalOp="Compute Scalar" LogicalOp="Compute Scalar" EstimateRows="4.03775" EstimateIO="0" EstimateCPU="4.03775e-007" AvgRowSize="11" EstimatedTotalSubtreeCost="0.0121915" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Column="Expr1004"></ColumnReference></OutputList><ComputeScalar><DefinedValues><DefinedValue><ColumnReference Column="Expr1004"></ColumnReference><ScalarOperator ScalarString="isnull([fiddle_0e375bcfb20b465d91e42ee832c7bc45].[dbo].[t1].[id],[fiddle_0e375bcfb20b465d91e42ee832c7bc45].[dbo].[t2].[id])"><Intrinsic FunctionName="isnull"><ScalarOperator><Identifier><ColumnReference Database="[fiddle_0e375bcfb20b465d91e42ee832c7bc45]" Schema="[dbo]" Table="[t1]" Column="id"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Identifier><ColumnReference Database="[fiddle_0e375bcfb20b465d91e42ee832c7bc45]" Schema="[dbo]" Table="[t2]" Column="id"></ColumnReference></Identifier></ScalarOperator></Intrinsic></ScalarOperator></DefinedValue></DefinedValues><RelOp NodeId="1" PhysicalOp="Filter" LogicalOp="Filter" EstimateRows="4.03775" EstimateIO="0" EstimateCPU="4.4e-006" AvgRowSize="15" EstimatedTotalSubtreeCost="0.0121911" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_0e375bcfb20b465d91e42ee832c7bc45]" Schema="[dbo]" Table="[t1]" Column="id"></ColumnReference><ColumnReference Database="[fiddle_0e375bcfb20b465d91e42ee832c7bc45]" Schema="[dbo]" Table="[t2]" Column="id"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="4" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><Filter StartupExpression="0"><RelOp NodeId="2" PhysicalOp="Merge Join" LogicalOp="Full Outer Join" EstimateRows="5" EstimateIO="0" EstimateCPU="0.0056131" AvgRowSize="15" EstimatedTotalSubtreeCost="0.0121867" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_0e375bcfb20b465d91e42ee832c7bc45]" Schema="[dbo]" Table="[t1]" Column="id"></ColumnReference><ColumnReference Database="[fiddle_0e375bcfb20b465d91e42ee832c7bc45]" Schema="[dbo]" Table="[t2]" Column="id"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="5" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualScans="0" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><Merge ManyToMany="0"><InnerSideJoinColumns><ColumnReference Database="[fiddle_0e375bcfb20b465d91e42ee832c7bc45]" Schema="[dbo]" Table="[t2]" Column="id"></ColumnReference></InnerSideJoinColumns><OuterSideJoinColumns><ColumnReference Database="[fiddle_0e375bcfb20b465d91e42ee832c7bc45]" Schema="[dbo]" Table="[t1]" Column="id"></ColumnReference></OuterSideJoinColumns><Residual><ScalarOperator ScalarString="[fiddle_0e375bcfb20b465d91e42ee832c7bc45].[dbo].[t1].[id]=[fiddle_0e375bcfb20b465d91e42ee832c7bc45].[dbo].[t2].[id]"><Compare CompareOp="EQ"><ScalarOperator><Identifier><ColumnReference Database="[fiddle_0e375bcfb20b465d91e42ee832c7bc45]" Schema="[dbo]" Table="[t1]" Column="id"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Identifier><ColumnReference Database="[fiddle_0e375bcfb20b465d91e42ee832c7bc45]" Schema="[dbo]" Table="[t2]" Column="id"></ColumnReference></Identifier></ScalarOperator></Compare></ScalarOperator></Residual><RelOp NodeId="3" PhysicalOp="Clustered Index Scan" LogicalOp="Clustered Index Scan" EstimateRows="3" EstimatedRowsRead="3" EstimateIO="0.003125" EstimateCPU="0.0001603" AvgRowSize="11" EstimatedTotalSubtreeCost="0.0032853" TableCardinality="3" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_0e375bcfb20b465d91e42ee832c7bc45]" Schema="[dbo]" Table="[t1]" Column="id"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="3" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualScans="1" ActualLogicalReads="2" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualRowsRead="3" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><IndexScan Ordered="1" ScanDirection="FORWARD" ForcedIndex="0" ForceSeek="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Database="[fiddle_0e375bcfb20b465d91e42ee832c7bc45]" Schema="[dbo]" Table="[t1]" Column="id"></ColumnReference></DefinedValue></DefinedValues><Object Database="[fiddle_0e375bcfb20b465d91e42ee832c7bc45]" Schema="[dbo]" Table="[t1]" Index="[PK__t1__3213E83FDF4FB5D1]" IndexKind="Clustered" Storage="RowStore"></Object></IndexScan></RelOp><RelOp NodeId="4" PhysicalOp="Clustered Index Scan" LogicalOp="Clustered Index Scan" EstimateRows="3" EstimatedRowsRead="3" EstimateIO="0.003125" EstimateCPU="0.0001603" AvgRowSize="11" EstimatedTotalSubtreeCost="0.0032853" TableCardinality="3" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_0e375bcfb20b465d91e42ee832c7bc45]" Schema="[dbo]" Table="[t2]" Column="id"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="3" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualScans="1" ActualLogicalReads="2" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualRowsRead="3" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><IndexScan Ordered="1" ScanDirection="FORWARD" ForcedIndex="0" ForceSeek="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Database="[fiddle_0e375bcfb20b465d91e42ee832c7bc45]" Schema="[dbo]" Table="[t2]" Column="id"></ColumnReference></DefinedValue></DefinedValues><Object Database="[fiddle_0e375bcfb20b465d91e42ee832c7bc45]" Schema="[dbo]" Table="[t2]" Index="[PK__t2__3213E83FA20DD0DE]" IndexKind="Clustered" Storage="RowStore"></Object></IndexScan></RelOp></Merge></RelOp><Predicate><ScalarOperator ScalarString="[fiddle_0e375bcfb20b465d91e42ee832c7bc45].[dbo].[t1].[id] IS NULL OR [fiddle_0e375bcfb20b465d91e42ee832c7bc45].[dbo].[t2].[id] IS NULL"><Logical Operation="OR"><ScalarOperator><Compare CompareOp="IS"><ScalarOperator><Identifier><ColumnReference Database="[fiddle_0e375bcfb20b465d91e42ee832c7bc45]" Schema="[dbo]" Table="[t1]" Column="id"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="NULL"></Const></ScalarOperator></Compare></ScalarOperator><ScalarOperator><Compare CompareOp="IS"><ScalarOperator><Identifier><ColumnReference Database="[fiddle_0e375bcfb20b465d91e42ee832c7bc45]" Schema="[dbo]" Table="[t2]" Column="id"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="NULL"></Const></ScalarOperator></Compare></ScalarOperator></Logical></ScalarOperator></Predicate></Filter></RelOp></ComputeScalar></RelOp></QueryPlan></StmtSimple></Statements></Batch></BatchSequence></ShowPlanXML>

set statistics io, xml on;
select id = isnull(t1.id,t2.id)
from t1 
  full outer join t2 
    on t1.id = t2.id
where t1.id is null 
  or t2.id is null

id

2

3

4

5

Microsoft SQL Server 2005 XML Showplan

<ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.5" Build="13.0.4001.0"><BatchSequence><Batch><Statements><StmtSimple StatementText="select id = isnull(t1.id,t2.id)&#xa;from t1 &#xa;  full outer join t2 &#xa;    on t1.id = t2.id&#xa;where t1.id is null &#xa;  or t2.id is null" StatementId="1" StatementCompId="2" StatementType="SELECT" RetrievedFromCache="true" StatementSubTreeCost="0.0121915" StatementEstRows="4.03775" SecurityPolicyApplied="false" StatementOptmLevel="FULL" QueryHash="0x9C95DE239E572541" QueryPlanHash="0xCE782233CF1522C3" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" CardinalityEstimationModelVersion="130"><StatementSetOptions QUOTED_IDENTIFIER="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" NUMERIC_ROUNDABORT="false"></StatementSetOptions><QueryPlan DegreeOfParallelism="0" NonParallelPlanReason="NoParallelPlansInDesktopOrExpressEdition" CachedPlanSize="24" CompileTime="0" CompileCPU="0" CompileMemory="240"><MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0"></MemoryGrantInfo><OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="419378" EstimatedPagesCached="26211" EstimatedAvailableDegreeOfParallelism="1" MaxCompileMemory="1007104"></OptimizerHardwareDependentProperties><TraceFlags IsCompileTime="1"><TraceFlag Value="8017" Scope="Global"></TraceFlag></TraceFlags><TraceFlags IsCompileTime="0"><TraceFlag Value="8017" Scope="Global"></TraceFlag></TraceFlags><QueryTimeStats ElapsedTime="0" CpuTime="0"></QueryTimeStats><RelOp NodeId="0" PhysicalOp="Compute Scalar" LogicalOp="Compute Scalar" EstimateRows="4.03775" EstimateIO="0" EstimateCPU="4.03775e-007" AvgRowSize="11" EstimatedTotalSubtreeCost="0.0121915" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Column="Expr1004"></ColumnReference></OutputList><ComputeScalar><DefinedValues><DefinedValue><ColumnReference Column="Expr1004"></ColumnReference><ScalarOperator ScalarString="isnull([fiddle_0e375bcfb20b465d91e42ee832c7bc45].[dbo].[t1].[id],[fiddle_0e375bcfb20b465d91e42ee832c7bc45].[dbo].[t2].[id])"><Intrinsic FunctionName="isnull"><ScalarOperator><Identifier><ColumnReference Database="[fiddle_0e375bcfb20b465d91e42ee832c7bc45]" Schema="[dbo]" Table="[t1]" Column="id"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Identifier><ColumnReference Database="[fiddle_0e375bcfb20b465d91e42ee832c7bc45]" Schema="[dbo]" Table="[t2]" Column="id"></ColumnReference></Identifier></ScalarOperator></Intrinsic></ScalarOperator></DefinedValue></DefinedValues><RelOp NodeId="1" PhysicalOp="Filter" LogicalOp="Filter" EstimateRows="4.03775" EstimateIO="0" EstimateCPU="4.4e-006" AvgRowSize="15" EstimatedTotalSubtreeCost="0.0121911" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_0e375bcfb20b465d91e42ee832c7bc45]" Schema="[dbo]" Table="[t1]" Column="id"></ColumnReference><ColumnReference Database="[fiddle_0e375bcfb20b465d91e42ee832c7bc45]" Schema="[dbo]" Table="[t2]" Column="id"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="4" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><Filter StartupExpression="0"><RelOp NodeId="2" PhysicalOp="Merge Join" LogicalOp="Full Outer Join" EstimateRows="5" EstimateIO="0" EstimateCPU="0.0056131" AvgRowSize="15" EstimatedTotalSubtreeCost="0.0121867" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_0e375bcfb20b465d91e42ee832c7bc45]" Schema="[dbo]" Table="[t1]" Column="id"></ColumnReference><ColumnReference Database="[fiddle_0e375bcfb20b465d91e42ee832c7bc45]" Schema="[dbo]" Table="[t2]" Column="id"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="5" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualScans="0" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><Merge ManyToMany="0"><InnerSideJoinColumns><ColumnReference Database="[fiddle_0e375bcfb20b465d91e42ee832c7bc45]" Schema="[dbo]" Table="[t2]" Column="id"></ColumnReference></InnerSideJoinColumns><OuterSideJoinColumns><ColumnReference Database="[fiddle_0e375bcfb20b465d91e42ee832c7bc45]" Schema="[dbo]" Table="[t1]" Column="id"></ColumnReference></OuterSideJoinColumns><Residual><ScalarOperator ScalarString="[fiddle_0e375bcfb20b465d91e42ee832c7bc45].[dbo].[t1].[id]=[fiddle_0e375bcfb20b465d91e42ee832c7bc45].[dbo].[t2].[id]"><Compare CompareOp="EQ"><ScalarOperator><Identifier><ColumnReference Database="[fiddle_0e375bcfb20b465d91e42ee832c7bc45]" Schema="[dbo]" Table="[t1]" Column="id"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Identifier><ColumnReference Database="[fiddle_0e375bcfb20b465d91e42ee832c7bc45]" Schema="[dbo]" Table="[t2]" Column="id"></ColumnReference></Identifier></ScalarOperator></Compare></ScalarOperator></Residual><RelOp NodeId="3" PhysicalOp="Clustered Index Scan" LogicalOp="Clustered Index Scan" EstimateRows="3" EstimatedRowsRead="3" EstimateIO="0.003125" EstimateCPU="0.0001603" AvgRowSize="11" EstimatedTotalSubtreeCost="0.0032853" TableCardinality="3" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_0e375bcfb20b465d91e42ee832c7bc45]" Schema="[dbo]" Table="[t1]" Column="id"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="3" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualScans="1" ActualLogicalReads="2" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualRowsRead="3" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><IndexScan Ordered="1" ScanDirection="FORWARD" ForcedIndex="0" ForceSeek="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Database="[fiddle_0e375bcfb20b465d91e42ee832c7bc45]" Schema="[dbo]" Table="[t1]" Column="id"></ColumnReference></DefinedValue></DefinedValues><Object Database="[fiddle_0e375bcfb20b465d91e42ee832c7bc45]" Schema="[dbo]" Table="[t1]" Index="[PK__t1__3213E83FDF4FB5D1]" IndexKind="Clustered" Storage="RowStore"></Object></IndexScan></RelOp><RelOp NodeId="4" PhysicalOp="Clustered Index Scan" LogicalOp="Clustered Index Scan" EstimateRows="3" EstimatedRowsRead="3" EstimateIO="0.003125" EstimateCPU="0.0001603" AvgRowSize="11" EstimatedTotalSubtreeCost="0.0032853" TableCardinality="3" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_0e375bcfb20b465d91e42ee832c7bc45]" Schema="[dbo]" Table="[t2]" Column="id"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="3" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualScans="1" ActualLogicalReads="2" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualRowsRead="3" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><IndexScan Ordered="1" ScanDirection="FORWARD" ForcedIndex="0" ForceSeek="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Database="[fiddle_0e375bcfb20b465d91e42ee832c7bc45]" Schema="[dbo]" Table="[t2]" Column="id"></ColumnReference></DefinedValue></DefinedValues><Object Database="[fiddle_0e375bcfb20b465d91e42ee832c7bc45]" Schema="[dbo]" Table="[t2]" Index="[PK__t2__3213E83FA20DD0DE]" IndexKind="Clustered" Storage="RowStore"></Object></IndexScan></RelOp></Merge></RelOp><Predicate><ScalarOperator ScalarString="[fiddle_0e375bcfb20b465d91e42ee832c7bc45].[dbo].[t1].[id] IS NULL OR [fiddle_0e375bcfb20b465d91e42ee832c7bc45].[dbo].[t2].[id] IS NULL"><Logical Operation="OR"><ScalarOperator><Compare CompareOp="IS"><ScalarOperator><Identifier><ColumnReference Database="[fiddle_0e375bcfb20b465d91e42ee832c7bc45]" Schema="[dbo]" Table="[t1]" Column="id"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="NULL"></Const></ScalarOperator></Compare></ScalarOperator><ScalarOperator><Compare CompareOp="IS"><ScalarOperator><Identifier><ColumnReference Database="[fiddle_0e375bcfb20b465d91e42ee832c7bc45]" Schema="[dbo]" Table="[t2]" Column="id"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="NULL"></Const></ScalarOperator></Compare></ScalarOperator></Logical></ScalarOperator></Predicate></Filter></RelOp></ComputeScalar></RelOp></QueryPlan></StmtSimple></Statements></Batch></BatchSequence></ShowPlanXML>

set statistics io, xml on;
select t1.id
from t1
  left join t2 on t1.id = t2.id
where t2.id is null
union
select t2.id
from t2
  left join t1 on t1.id = t2.id
where t1.id is null

id

2

3

4

5

Microsoft SQL Server 2005 XML Showplan

<ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.5" Build="13.0.4001.0"><BatchSequence><Batch><Statements><StmtSimple StatementText="select t1.id&#xa;from t1&#xa;  left join t2 on t1.id = t2.id&#xa;where t2.id is null&#xa;union&#xa;select t2.id&#xa;from t2&#xa;  left join t1 on t1.id = t2.id&#xa;where t1.id is null" StatementId="1" StatementCompId="2" StatementType="SELECT" RetrievedFromCache="true" StatementSubTreeCost="0.0191604" StatementEstRows="1" SecurityPolicyApplied="false" StatementOptmLevel="FULL" QueryHash="0x5E28C3781C13B1CC" QueryPlanHash="0xE6AACFA768ABA27C" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" CardinalityEstimationModelVersion="130"><StatementSetOptions QUOTED_IDENTIFIER="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" NUMERIC_ROUNDABORT="false"></StatementSetOptions><QueryPlan DegreeOfParallelism="0" NonParallelPlanReason="NoParallelPlansInDesktopOrExpressEdition" CachedPlanSize="24" CompileTime="0" CompileCPU="0" CompileMemory="288"><MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0"></MemoryGrantInfo><OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="419378" EstimatedPagesCached="26211" EstimatedAvailableDegreeOfParallelism="1" MaxCompileMemory="1007104"></OptimizerHardwareDependentProperties><TraceFlags IsCompileTime="1"><TraceFlag Value="8017" Scope="Global"></TraceFlag></TraceFlags><TraceFlags IsCompileTime="0"><TraceFlag Value="8017" Scope="Global"></TraceFlag></TraceFlags><QueryTimeStats ElapsedTime="0" CpuTime="0"></QueryTimeStats><RelOp NodeId="0" PhysicalOp="Merge Join" LogicalOp="Union" EstimateRows="1" EstimateIO="0" EstimateCPU="0.0056085" AvgRowSize="11" EstimatedTotalSubtreeCost="0.0191604" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Column="Union1008"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="4" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualScans="0" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><Merge><DefinedValues><DefinedValue><ColumnReference Column="Union1008"></ColumnReference><ColumnReference Database="[fiddle_0e375bcfb20b465d91e42ee832c7bc45]" Schema="[dbo]" Table="[t1]" Column="id"></ColumnReference><ColumnReference Database="[fiddle_0e375bcfb20b465d91e42ee832c7bc45]" Schema="[dbo]" Table="[t2]" Column="id"></ColumnReference></DefinedValue></DefinedValues><RelOp NodeId="1" PhysicalOp="Filter" LogicalOp="Filter" EstimateRows="2" EstimateIO="0" EstimateCPU="1.44e-006" AvgRowSize="11" EstimatedTotalSubtreeCost="0.00677596" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_0e375bcfb20b465d91e42ee832c7bc45]" Schema="[dbo]" Table="[t1]" Column="id"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="2" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><Filter StartupExpression="0"><RelOp NodeId="2" PhysicalOp="Nested Loops" LogicalOp="Left Outer Join" EstimateRows="3" EstimateIO="0" EstimateCPU="3.762e-005" AvgRowSize="15" EstimatedTotalSubtreeCost="0.00677452" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_0e375bcfb20b465d91e42ee832c7bc45]" Schema="[dbo]" Table="[t1]" Column="id"></ColumnReference><ColumnReference Database="[fiddle_0e375bcfb20b465d91e42ee832c7bc45]" Schema="[dbo]" Table="[t2]" Column="id"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="3" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><NestedLoops Optimized="0"><Predicate><ScalarOperator ScalarString="[fiddle_0e375bcfb20b465d91e42ee832c7bc45].[dbo].[t1].[id]=[fiddle_0e375bcfb20b465d91e42ee832c7bc45].[dbo].[t2].[id]"><Compare CompareOp="EQ"><ScalarOperator><Identifier><ColumnReference Database="[fiddle_0e375bcfb20b465d91e42ee832c7bc45]" Schema="[dbo]" Table="[t1]" Column="id"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Identifier><ColumnReference Database="[fiddle_0e375bcfb20b465d91e42ee832c7bc45]" Schema="[dbo]" Table="[t2]" Column="id"></ColumnReference></Identifier></ScalarOperator></Compare></ScalarOperator></Predicate><RelOp NodeId="3" PhysicalOp="Clustered Index Scan" LogicalOp="Clustered Index Scan" EstimateRows="3" EstimatedRowsRead="3" EstimateIO="0.003125" EstimateCPU="0.0001603" AvgRowSize="11" EstimatedTotalSubtreeCost="0.0032853" TableCardinality="3" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_0e375bcfb20b465d91e42ee832c7bc45]" Schema="[dbo]" Table="[t1]" Column="id"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="3" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualScans="1" ActualLogicalReads="2" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualRowsRead="3" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><IndexScan Ordered="1" ScanDirection="FORWARD" ForcedIndex="0" ForceSeek="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Database="[fiddle_0e375bcfb20b465d91e42ee832c7bc45]" Schema="[dbo]" Table="[t1]" Column="id"></ColumnReference></DefinedValue></DefinedValues><Object Database="[fiddle_0e375bcfb20b465d91e42ee832c7bc45]" Schema="[dbo]" Table="[t1]" Index="[PK__t1__3213E83FDF4FB5D1]" TableReferenceId="1" IndexKind="Clustered" Storage="RowStore"></Object></IndexScan></RelOp><RelOp NodeId="4" PhysicalOp="Clustered Index Scan" LogicalOp="Clustered Index Scan" EstimateRows="3" EstimatedRowsRead="3" EstimateIO="0.0032035" EstimateCPU="8.18e-005" AvgRowSize="11" EstimatedTotalSubtreeCost="0.0034489" TableCardinality="3" Parallel="0" EstimateRebinds="0" EstimateRewinds="2" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_0e375bcfb20b465d91e42ee832c7bc45]" Schema="[dbo]" Table="[t2]" Column="id"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="9" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualScans="1" ActualLogicalReads="7" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualRowsRead="9" ActualEndOfScans="3" ActualExecutions="3"></RunTimeCountersPerThread></RunTimeInformation><IndexScan Ordered="0" ForcedIndex="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Database="[fiddle_0e375bcfb20b465d91e42ee832c7bc45]" Schema="[dbo]" Table="[t2]" Column="id"></ColumnReference></DefinedValue></DefinedValues><Object Database="[fiddle_0e375bcfb20b465d91e42ee832c7bc45]" Schema="[dbo]" Table="[t2]" Index="[PK__t2__3213E83FA20DD0DE]" TableReferenceId="1" IndexKind="Clustered" Storage="RowStore"></Object></IndexScan></RelOp></NestedLoops></RelOp><Predicate><ScalarOperator ScalarString="[fiddle_0e375bcfb20b465d91e42ee832c7bc45].[dbo].[t2].[id] IS NULL"><Compare CompareOp="IS"><ScalarOperator><Identifier><ColumnReference Database="[fiddle_0e375bcfb20b465d91e42ee832c7bc45]" Schema="[dbo]" Table="[t2]" Column="id"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="NULL"></Const></ScalarOperator></Compare></ScalarOperator></Predicate></Filter></RelOp><RelOp NodeId="5" PhysicalOp="Filter" LogicalOp="Filter" EstimateRows="2" EstimateIO="0" EstimateCPU="1.44e-006" AvgRowSize="11" EstimatedTotalSubtreeCost="0.00677596" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_0e375bcfb20b465d91e42ee832c7bc45]" Schema="[dbo]" Table="[t2]" Column="id"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="2" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><Filter StartupExpression="0"><RelOp NodeId="6" PhysicalOp="Nested Loops" LogicalOp="Left Outer Join" EstimateRows="3" EstimateIO="0" EstimateCPU="3.762e-005" AvgRowSize="15" EstimatedTotalSubtreeCost="0.00677452" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_0e375bcfb20b465d91e42ee832c7bc45]" Schema="[dbo]" Table="[t2]" Column="id"></ColumnReference><ColumnReference Database="[fiddle_0e375bcfb20b465d91e42ee832c7bc45]" Schema="[dbo]" Table="[t1]" Column="id"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="3" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><NestedLoops Optimized="0"><Predicate><ScalarOperator ScalarString="[fiddle_0e375bcfb20b465d91e42ee832c7bc45].[dbo].[t1].[id]=[fiddle_0e375bcfb20b465d91e42ee832c7bc45].[dbo].[t2].[id]"><Compare CompareOp="EQ"><ScalarOperator><Identifier><ColumnReference Database="[fiddle_0e375bcfb20b465d91e42ee832c7bc45]" Schema="[dbo]" Table="[t1]" Column="id"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Identifier><ColumnReference Database="[fiddle_0e375bcfb20b465d91e42ee832c7bc45]" Schema="[dbo]" Table="[t2]" Column="id"></ColumnReference></Identifier></ScalarOperator></Compare></ScalarOperator></Predicate><RelOp NodeId="7" PhysicalOp="Clustered Index Scan" LogicalOp="Clustered Index Scan" EstimateRows="3" EstimatedRowsRead="3" EstimateIO="0.003125" EstimateCPU="0.0001603" AvgRowSize="11" EstimatedTotalSubtreeCost="0.0032853" TableCardinality="3" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_0e375bcfb20b465d91e42ee832c7bc45]" Schema="[dbo]" Table="[t2]" Column="id"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="3" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualScans="1" ActualLogicalReads="2" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualRowsRead="3" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><IndexScan Ordered="1" ScanDirection="FORWARD" ForcedIndex="0" ForceSeek="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Database="[fiddle_0e375bcfb20b465d91e42ee832c7bc45]" Schema="[dbo]" Table="[t2]" Column="id"></ColumnReference></DefinedValue></DefinedValues><Object Database="[fiddle_0e375bcfb20b465d91e42ee832c7bc45]" Schema="[dbo]" Table="[t2]" Index="[PK__t2__3213E83FA20DD0DE]" TableReferenceId="2" IndexKind="Clustered" Storage="RowStore"></Object></IndexScan></RelOp><RelOp NodeId="8" PhysicalOp="Clustered Index Scan" LogicalOp="Clustered Index Scan" EstimateRows="3" EstimatedRowsRead="3" EstimateIO="0.0032035" EstimateCPU="8.18e-005" AvgRowSize="11" EstimatedTotalSubtreeCost="0.0034489" TableCardinality="3" Parallel="0" EstimateRebinds="0" EstimateRewinds="2" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_0e375bcfb20b465d91e42ee832c7bc45]" Schema="[dbo]" Table="[t1]" Column="id"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="9" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualScans="1" ActualLogicalReads="7" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualRowsRead="9" ActualEndOfScans="3" ActualExecutions="3"></RunTimeCountersPerThread></RunTimeInformation><IndexScan Ordered="0" ForcedIndex="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Database="[fiddle_0e375bcfb20b465d91e42ee832c7bc45]" Schema="[dbo]" Table="[t1]" Column="id"></ColumnReference></DefinedValue></DefinedValues><Object Database="[fiddle_0e375bcfb20b465d91e42ee832c7bc45]" Schema="[dbo]" Table="[t1]" Index="[PK__t1__3213E83FDF4FB5D1]" TableReferenceId="2" IndexKind="Clustered" Storage="RowStore"></Object></IndexScan></RelOp></NestedLoops></RelOp><Predicate><ScalarOperator ScalarString="[fiddle_0e375bcfb20b465d91e42ee832c7bc45].[dbo].[t1].[id] IS NULL"><Compare CompareOp="IS"><ScalarOperator><Identifier><ColumnReference Database="[fiddle_0e375bcfb20b465d91e42ee832c7bc45]" Schema="[dbo]" Table="[t1]" Column="id"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="NULL"></Const></ScalarOperator></Compare></ScalarOperator></Predicate></Filter></RelOp></Merge></RelOp></QueryPlan></StmtSimple></Statements></Batch></BatchSequence></ShowPlanXML>

set statistics io, xml on;
select t1.id
from t1
  left join t2 on t1.id = t2.id
where t2.id is null
union all
select t2.id
from t2
  left join t1 on t1.id = t2.id
where t1.id is null

id

2

3

4

5

Microsoft SQL Server 2005 XML Showplan

<ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.5" Build="13.0.4001.0"><BatchSequence><Batch><Statements><StmtSimple StatementText="select t1.id&#xa;from t1&#xa;  left join t2 on t1.id = t2.id&#xa;where t2.id is null&#xa;union all&#xa;select t2.id&#xa;from t2&#xa;  left join t1 on t1.id = t2.id&#xa;where t1.id is null" StatementId="1" StatementCompId="2" StatementType="SELECT" RetrievedFromCache="true" StatementSubTreeCost="0.0135523" StatementEstRows="4" SecurityPolicyApplied="false" StatementOptmLevel="FULL" QueryHash="0x16F0B6FFB7E845F7" QueryPlanHash="0xD2425E11F664F13B" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" CardinalityEstimationModelVersion="130"><StatementSetOptions QUOTED_IDENTIFIER="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" NUMERIC_ROUNDABORT="false"></StatementSetOptions><QueryPlan DegreeOfParallelism="0" NonParallelPlanReason="NoParallelPlansInDesktopOrExpressEdition" CachedPlanSize="24" CompileTime="0" CompileCPU="0" CompileMemory="248"><MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0"></MemoryGrantInfo><OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="419378" EstimatedPagesCached="26211" EstimatedAvailableDegreeOfParallelism="1" MaxCompileMemory="1007104"></OptimizerHardwareDependentProperties><TraceFlags IsCompileTime="1"><TraceFlag Value="8017" Scope="Global"></TraceFlag></TraceFlags><TraceFlags IsCompileTime="0"><TraceFlag Value="8017" Scope="Global"></TraceFlag></TraceFlags><QueryTimeStats ElapsedTime="0" CpuTime="0"></QueryTimeStats><RelOp NodeId="0" PhysicalOp="Concatenation" LogicalOp="Concatenation" EstimateRows="4" EstimateIO="0" EstimateCPU="4e-007" AvgRowSize="11" EstimatedTotalSubtreeCost="0.0135523" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Column="Union1008"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="4" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><Concat><DefinedValues><DefinedValue><ColumnReference Column="Union1008"></ColumnReference><ColumnReference Database="[fiddle_0e375bcfb20b465d91e42ee832c7bc45]" Schema="[dbo]" Table="[t1]" Column="id"></ColumnReference><ColumnReference Database="[fiddle_0e375bcfb20b465d91e42ee832c7bc45]" Schema="[dbo]" Table="[t2]" Column="id"></ColumnReference></DefinedValue></DefinedValues><RelOp NodeId="1" PhysicalOp="Filter" LogicalOp="Filter" EstimateRows="2" EstimateIO="0" EstimateCPU="1.44e-006" AvgRowSize="11" EstimatedTotalSubtreeCost="0.00677596" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_0e375bcfb20b465d91e42ee832c7bc45]" Schema="[dbo]" Table="[t1]" Column="id"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="2" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><Filter StartupExpression="0"><RelOp NodeId="2" PhysicalOp="Nested Loops" LogicalOp="Left Outer Join" EstimateRows="3" EstimateIO="0" EstimateCPU="3.762e-005" AvgRowSize="15" EstimatedTotalSubtreeCost="0.00677452" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_0e375bcfb20b465d91e42ee832c7bc45]" Schema="[dbo]" Table="[t1]" Column="id"></ColumnReference><ColumnReference Database="[fiddle_0e375bcfb20b465d91e42ee832c7bc45]" Schema="[dbo]" Table="[t2]" Column="id"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="3" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><NestedLoops Optimized="0"><Predicate><ScalarOperator ScalarString="[fiddle_0e375bcfb20b465d91e42ee832c7bc45].[dbo].[t1].[id]=[fiddle_0e375bcfb20b465d91e42ee832c7bc45].[dbo].[t2].[id]"><Compare CompareOp="EQ"><ScalarOperator><Identifier><ColumnReference Database="[fiddle_0e375bcfb20b465d91e42ee832c7bc45]" Schema="[dbo]" Table="[t1]" Column="id"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Identifier><ColumnReference Database="[fiddle_0e375bcfb20b465d91e42ee832c7bc45]" Schema="[dbo]" Table="[t2]" Column="id"></ColumnReference></Identifier></ScalarOperator></Compare></ScalarOperator></Predicate><RelOp NodeId="3" PhysicalOp="Clustered Index Scan" LogicalOp="Clustered Index Scan" EstimateRows="3" EstimatedRowsRead="3" EstimateIO="0.003125" EstimateCPU="0.0001603" AvgRowSize="11" EstimatedTotalSubtreeCost="0.0032853" TableCardinality="3" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_0e375bcfb20b465d91e42ee832c7bc45]" Schema="[dbo]" Table="[t1]" Column="id"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="3" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualScans="1" ActualLogicalReads="2" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualRowsRead="3" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><IndexScan Ordered="0" ForcedIndex="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Database="[fiddle_0e375bcfb20b465d91e42ee832c7bc45]" Schema="[dbo]" Table="[t1]" Column="id"></ColumnReference></DefinedValue></DefinedValues><Object Database="[fiddle_0e375bcfb20b465d91e42ee832c7bc45]" Schema="[dbo]" Table="[t1]" Index="[PK__t1__3213E83FDF4FB5D1]" TableReferenceId="1" IndexKind="Clustered" Storage="RowStore"></Object></IndexScan></RelOp><RelOp NodeId="4" PhysicalOp="Clustered Index Scan" LogicalOp="Clustered Index Scan" EstimateRows="3" EstimatedRowsRead="3" EstimateIO="0.0032035" EstimateCPU="8.18e-005" AvgRowSize="11" EstimatedTotalSubtreeCost="0.0034489" TableCardinality="3" Parallel="0" EstimateRebinds="0" EstimateRewinds="2" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_0e375bcfb20b465d91e42ee832c7bc45]" Schema="[dbo]" Table="[t2]" Column="id"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="9" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualScans="1" ActualLogicalReads="7" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualRowsRead="9" ActualEndOfScans="3" ActualExecutions="3"></RunTimeCountersPerThread></RunTimeInformation><IndexScan Ordered="0" ForcedIndex="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Database="[fiddle_0e375bcfb20b465d91e42ee832c7bc45]" Schema="[dbo]" Table="[t2]" Column="id"></ColumnReference></DefinedValue></DefinedValues><Object Database="[fiddle_0e375bcfb20b465d91e42ee832c7bc45]" Schema="[dbo]" Table="[t2]" Index="[PK__t2__3213E83FA20DD0DE]" TableReferenceId="1" IndexKind="Clustered" Storage="RowStore"></Object></IndexScan></RelOp></NestedLoops></RelOp><Predicate><ScalarOperator ScalarString="[fiddle_0e375bcfb20b465d91e42ee832c7bc45].[dbo].[t2].[id] IS NULL"><Compare CompareOp="IS"><ScalarOperator><Identifier><ColumnReference Database="[fiddle_0e375bcfb20b465d91e42ee832c7bc45]" Schema="[dbo]" Table="[t2]" Column="id"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="NULL"></Const></ScalarOperator></Compare></ScalarOperator></Predicate></Filter></RelOp><RelOp NodeId="5" PhysicalOp="Filter" LogicalOp="Filter" EstimateRows="2" EstimateIO="0" EstimateCPU="1.44e-006" AvgRowSize="11" EstimatedTotalSubtreeCost="0.00677596" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_0e375bcfb20b465d91e42ee832c7bc45]" Schema="[dbo]" Table="[t2]" Column="id"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="2" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><Filter StartupExpression="0"><RelOp NodeId="6" PhysicalOp="Nested Loops" LogicalOp="Left Outer Join" EstimateRows="3" EstimateIO="0" EstimateCPU="3.762e-005" AvgRowSize="15" EstimatedTotalSubtreeCost="0.00677452" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_0e375bcfb20b465d91e42ee832c7bc45]" Schema="[dbo]" Table="[t2]" Column="id"></ColumnReference><ColumnReference Database="[fiddle_0e375bcfb20b465d91e42ee832c7bc45]" Schema="[dbo]" Table="[t1]" Column="id"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="3" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><NestedLoops Optimized="0"><Predicate><ScalarOperator ScalarString="[fiddle_0e375bcfb20b465d91e42ee832c7bc45].[dbo].[t1].[id]=[fiddle_0e375bcfb20b465d91e42ee832c7bc45].[dbo].[t2].[id]"><Compare CompareOp="EQ"><ScalarOperator><Identifier><ColumnReference Database="[fiddle_0e375bcfb20b465d91e42ee832c7bc45]" Schema="[dbo]" Table="[t1]" Column="id"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Identifier><ColumnReference Database="[fiddle_0e375bcfb20b465d91e42ee832c7bc45]" Schema="[dbo]" Table="[t2]" Column="id"></ColumnReference></Identifier></ScalarOperator></Compare></ScalarOperator></Predicate><RelOp NodeId="7" PhysicalOp="Clustered Index Scan" LogicalOp="Clustered Index Scan" EstimateRows="3" EstimatedRowsRead="3" EstimateIO="0.003125" EstimateCPU="0.0001603" AvgRowSize="11" EstimatedTotalSubtreeCost="0.0032853" TableCardinality="3" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_0e375bcfb20b465d91e42ee832c7bc45]" Schema="[dbo]" Table="[t2]" Column="id"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="3" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualScans="1" ActualLogicalReads="2" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualRowsRead="3" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><IndexScan Ordered="0" ForcedIndex="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Database="[fiddle_0e375bcfb20b465d91e42ee832c7bc45]" Schema="[dbo]" Table="[t2]" Column="id"></ColumnReference></DefinedValue></DefinedValues><Object Database="[fiddle_0e375bcfb20b465d91e42ee832c7bc45]" Schema="[dbo]" Table="[t2]" Index="[PK__t2__3213E83FA20DD0DE]" TableReferenceId="2" IndexKind="Clustered" Storage="RowStore"></Object></IndexScan></RelOp><RelOp NodeId="8" PhysicalOp="Clustered Index Scan" LogicalOp="Clustered Index Scan" EstimateRows="3" EstimatedRowsRead="3" EstimateIO="0.0032035" EstimateCPU="8.18e-005" AvgRowSize="11" EstimatedTotalSubtreeCost="0.0034489" TableCardinality="3" Parallel="0" EstimateRebinds="0" EstimateRewinds="2" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_0e375bcfb20b465d91e42ee832c7bc45]" Schema="[dbo]" Table="[t1]" Column="id"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="9" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualScans="1" ActualLogicalReads="7" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualRowsRead="9" ActualEndOfScans="3" ActualExecutions="3"></RunTimeCountersPerThread></RunTimeInformation><IndexScan Ordered="0" ForcedIndex="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Database="[fiddle_0e375bcfb20b465d91e42ee832c7bc45]" Schema="[dbo]" Table="[t1]" Column="id"></ColumnReference></DefinedValue></DefinedValues><Object Database="[fiddle_0e375bcfb20b465d91e42ee832c7bc45]" Schema="[dbo]" Table="[t1]" Index="[PK__t1__3213E83FDF4FB5D1]" TableReferenceId="2" IndexKind="Clustered" Storage="RowStore"></Object></IndexScan></RelOp></NestedLoops></RelOp><Predicate><ScalarOperator ScalarString="[fiddle_0e375bcfb20b465d91e42ee832c7bc45].[dbo].[t1].[id] IS NULL"><Compare CompareOp="IS"><ScalarOperator><Identifier><ColumnReference Database="[fiddle_0e375bcfb20b465d91e42ee832c7bc45]" Schema="[dbo]" Table="[t1]" Column="id"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="NULL"></Const></ScalarOperator></Compare></ScalarOperator></Predicate></Filter></RelOp></Concat></RelOp></QueryPlan></StmtSimple></Statements></Batch></BatchSequence></ShowPlanXML>