1 of 5

This says to me a subquery (which always returns a single value) represents an empty rowset as null in that return value, but attempting to select a single column from an empty rowset does not return null. Rather, it kind of says "I have no value, so there is nothing to assign".

I think you are looking at both cases incorrectly.

  1. A subquery does not return does not return a result set. It returns a scalar value. When no scalar value exists, it returns NULL for precisely the reason you thought: it means "unknown". Think of a subquery as being a replacement for a scalar UDF. It will always return a single something (T-SQL does not allow for a return type of void). So, what you have is effectively: select @testB = dbo.Function(6); assuming that "dbo.Function" is defined as:

    CREATE FUNCTION dbo.Function (@Input INT)
    RETURNS INT
    AS
    BEGIN
      DECLARE @Temp INT;
      SELECT @Temp = TestTable.[id]
      FROM   (VALUES (5)) TestTable([id])
      WHERE  TestTable.[id] = @Input;
    
      RETURN @Temp;
    END;
    
  2. I wouldn't think of an empty result set in terms of "I have no value, so there is nothing to assign". Instead, think of it in terms of: an assignment will be performed for every row returned, and no rows returned means that there is no action to take. It might be a subtle difference, but the focus really needs to be on "rows = actions" more so than "returned value is something that can be assigned".