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 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".
This distinction is not so subtle when:
- You are executing a T-SQL scalar UDF in a query and passing in a column. Assuming we are not dealing with Scalar UDF inlining (starting in SQL Server 2019), then scalar UDFs are executed per each row, hence the performance issues associated with them and the impetus for the new feature in SQL Server 2019 to inline UDFs (if they meet the requirements).
You are setting a value like you are showing for
select @testA = id from .... The assignment will happen per each and every row returned. And the final value of the variable will be the last row returned. That can be predictable if you use an
ORDER BY, or you can live life on the edge and go for unpredictable results by not using an
ORDER BY. For example:
DECLARE @Temp INT; SELECT @Temp = tab.col FROM (VALUES (1), (3), (4), (2)) tab(col); SELECT @Temp AS [NotOrdered]; -- 2 SELECT @Temp = tab.col FROM (VALUES (1), (3), (4), (2)) tab(col) ORDER BY tab.col; SELECT @Temp AS [Ordered]; -- 4