EXCEPT & INTERSECT: Purpose of Passive Projection in Logical Plan

There are various things we do inside the query optimizer that don't really have a reason that we can explain externally. The projects you see in the optimizer eventually get rewritten at the end of the optimizer to "flatten" the expressions. So, the optimizer has things in the search that are part of how the code is implemented that may introduce extra projects that don't really do anything functionally but do allow us to stitch things together where one subtree was created in one part of the optimizer but is used later in a place where it was not originally intended. There are some physical implementation details that can cause this to happen. None of them matter for users in terms of plan quality (in almost any case - we have done work to make these not matter in the search).

So, I'd just wave my hand and say "you don't need to see his identification. He can go about his business. Move along" ;).

I don't have a fully satisfying answer to this, but DISTINCT is translated to a project plus group-by aggregate.

Both EXCEPT and INTERSECT come with an implied DISTINCT on the first table expression. It is this DISTINCT that results in the 'blank' project in the tree. It is harmless.

If you write a DISTINCT as the equivalent GROUP BY, you don't see the blank project. The project appears after the aggregate:

SELECT DISTINCT P.ProductID
FROM Production.Product AS P
OPTION (QUERYTRACEON 3604, QUERYTRACEON 8605);

LogOp_GbAgg OUT(QCOL: [P].ProductID,) BY(QCOL: [P].ProductID,)
    LogOp_Project
        LogOp_Project QCOL: [P].ProductID
            LogOp_Get TBL: Production.Product(alias TBL: P)
            AncOp_PrjList 
        AncOp_PrjList 
    AncOp_PrjList

SELECT P.ProductID
FROM Production.Product AS P
GROUP BY p.ProductID
OPTION (QUERYTRACEON 3604, QUERYTRACEON 8605);

LogOp_Project QCOL: [P].ProductID
    LogOp_GbAgg OUT(QCOL: [P].ProductID,) BY(QCOL: [P].ProductID,)
        LogOp_Project
            LogOp_Get TBL: Production.Product(alias TBL: P)
            AncOp_PrjList 
        AncOp_PrjList 
    AncOp_PrjList