Sample Header Ad - 728x90

EXCEPT & INTERSECT: Purpose of Passive Projection in Logical Plan

9 votes
2 answers
614 views
The following EXCEPT query produces a logical plan with a seemingly purposeless projection. This also occurs for INTERSECT. What is the purpose of the projection? e.g. Is there a different EXCEPT query where the outer projection would specify something? Query:
use AdventureWorks2017

select p.ProductId
from Production.Product as p
except
select pinv.ProductID
from Production.ProductInventory as pinv
option (recompile, querytraceon 8605, querytraceon 3604)
Converted Tree:
LogOp_Select

        LogOp_GbAgg OUT(QCOL: [p].ProductID,) BY(QCOL: [p].ProductID,)

            LogOp_Project -- << ?? PASSIVE PROJECTION ??

                LogOp_Project

                    LogOp_Get TBL: Production.Product(alias TBL: p) Production.Product TableID=482100758 TableReferenceID=0 IsRow: COL: IsBaseRow1000 

                    AncOp_PrjList 

                AncOp_PrjList 

            AncOp_PrjList 

        ScaOp_Exists 

            LogOp_Select

                LogOp_Project

                    LogOp_Get TBL: Production.ProductInventory(alias TBL: pinv) Production.ProductInventory TableID=914102297 TableReferenceID=0 IsRow: COL: IsBaseRow1001 

                    AncOp_PrjList 

                ScaOp_Comp x_cmpEq

                    ScaOp_Identifier QCOL: [p].ProductID

                    ScaOp_Identifier QCOL: [pinv].ProductID
Asked by Paul Holmes (889 rep)
Apr 16, 2020, 03:09 AM
Last activity: Apr 17, 2020, 11:55 AM