This says to me a subquery (which always returns a single value) represents an empty rowset as
nullin 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.
A subquery does not return does not return a result set. It returns a scalar value. When no scalar value exists, it returns
NULLfor 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;
- 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".