sql server: why is an empty row set not equivalent to null when assigning a query result to a variable and what is the best way to solve it?

I have a cursor inside my loop select Single column and assign the result to a variable. When the select returns an empty rowset, nothing is assigned to the variable. In particular, the variable retains its current value which, in my case, is the value it had in the last iteration of the loop.

The following code illustrates this.

declare @testA int;
declare @testB int;

set @testA = 1;
set @testB = 2;

select @testA = id from (values ​​(3)) as testtable (id) where testtable.id = 4; - empty rows
select @testB = (select id from (values ​​(5)) as testtable (id) where testtable.id = 6); - empty rows

select @testA as testA, @testB as testB;

@head retained its value (1) while @testB was updated to null.

The only difference between the two assignment attempts was that in case B a subquery was used (through the addition of the parentheses and an additional reference). select keyword).

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

My understanding of the definition of null It is that it represents an "undefined" value.

If I'm saying "select & # 39; id & # 39; where these conditions are met" and there are no values ​​of & # 39; id & # 39; If they meet these conditions, does this not imply that the value of & id = 39; What I want is not defined?