Where do this Constant Scan and Left Outer Join come from in a trivial SELECT query plan?

I have this table:

CREATE TABLE [dbo].[Accounts] (
    [AccountId] UNIQUEIDENTIFIER UNIQUE NOT NULL DEFAULT NEWID(),
    -- WHATEVER other columns
);
GO
CREATE UNIQUE CLUSTERED INDEX [AccountsIndex]
    ON [dbo].[Accounts]([AccountId] ASC);
GO

This query:

DECLARE @result UNIQUEIDENTIFIER
SELECT @result = AccountId FROM Accounts WHERE AccountId='guid-here'

executes with a query plan consisting of a single Index Seek - as expected:

SELECT <---- Clustered Index Seek

This query does the same:

DECLARE @result UNIQUEIDENTIFIER
SET @result = (SELECT AccountId FROM Accounts WHERE AccountId='guid-here')

but it's executed with a plan where result of Index Seek is Left Outer Joined with result of some Constant Scan and then fed into Compute Scalar:

SELECT <--- Compute Scalar <--- Left Outer Join <--- Constant Scan
                                      ^
                                      |------Clustered Index Seek

What's that extra magic? What does that Constant Scan followed by Left Outer Join do?

The semantics of the two statements are different:

• The first does not set the value of the variable if no row is found.

• The second always sets the variable, including to null if no row is found.

The Constant Scan produces an empty row (with no columns!) that will result in the variable being updated in case nothing matches from the base table. The left join ensures the empty row survives the join. Variable assignment can be thought of as happening at the root node of the execution plan.
Using SELECT @result

-- Set initial value
DECLARE @result uniqueidentifier = {guid 'FE2CA909-1162-4C6C-A7AC-33B257E28539'};

-- @result does not change
SELECT @result = AccountId 
FROM Accounts 
WHERE AccountId={guid '7AD4D33C-1ED7-4183-B7F3-48C33D666525'};

SELECT @result;

Using SET @result

-- Set initial value
DECLARE @result uniqueidentifier = {guid 'FE2CA909-1162-4C6C-A7AC-33B257E28539'};

-- @result set to null
SET @result = 
(
    SELECT AccountId 
    FROM Accounts 
    WHERE AccountId={guid '7AD4D33C-1ED7-4183-B7F3-48C33D666525'}
);

SELECT @result;

Execution plans

No row arrives at the root node, so no assignment occurs.

A row always arrives at the root node, so variable assignment occurs.

The extra Constant Scan and Nested Loops Left Outer Join are nothing to be concerned about. The join in particular is cheap since it is guaranteed to encounter one row on its outer input, and at most one row (in your example) on the inner input.

There are other ways to ensure a row is generated from the subquery to ensure a variable assignment occurs. One is to use a redundant scalar aggregate (no group by clause):

-- Set initial value
DECLARE @result uniqueidentifier = {guid 'FE2CA909-1162-4C6C-A7AC-33B257E28539'};

-- @result set to null
SET @result = 
    (
        SELECT MAX(AccountId)
        FROM Accounts 
        WHERE AccountId={guid '7AD4D33C-1ED7-4183-B7F3-48C33D666525'} 
    );
SELECT @result;

Notice the scalar aggregate produces a row even though it receives no input.

Documentation:

• SET @local_variable (Transact-SQL)

• SELECT @local_variable (Transact-SQL)

If the SELECT statement returns no rows, the variable retains its present value. If expression is a scalar subquery that returns no value, the variable is set to NULL.

For assigning variables, we recommend that you use SET @local_variable instead of SELECT @local_variable.

Further reading:

• Fun with Scalar and Vector Aggregates

StackExchange Database Administrators Q#136210, answer score: 29