An issue to be aware of when assigning variables to a variable using SELECT statements in SQL Server

We know that in SQL Server you can assign a value to a variable using a SELECT statement, such as the following statement to assign a variable @id of type int

1 Declare @id int=-1;2 3 Select @id=Id from 4 (5 Select 1  asID6 Union  All 7 Select 2  asID8 Union  All 9 Select 3  asIDTen) asT One  A Select @id

Executing the above code shows the results of the following query, and the result shows that the last @id value is 3, which means that for each row of data records returned by the SELECT statement on line 3rd, SQL Server assigns an ID column @id, and the last row of data record ID is 3. So in the 12th row of the query, the last to find @id 3

So what would @id be in a 12-row query if the query statement on line 3rd did not return a row of results? Many people may think that @id will be null.

Next we will change the above code slightly as follows, we added a where condition 1<>1 in the query of line 3rd, this condition is never satisfied always false, so now the 3rd row of the SELECT statement a record will not return

1 Declare @id int=-1;2 3 Select @id=Id from 4 (5 Select 1  asID6 Union  All 7 Select 2  asID8 Union  All 9 Select 3  asIDTen) asT One where 1<>1 A  - Select @id

We look at the execution of the code now, we find that the result is not NULL but 1, the 3rd row of the SELECT statement root is not assigned to @id, the reason is very simple, because we said the 3rd row of the SELECT statement returns how many records, the @id will be assigned how many times the value, Now that it does not return a row of records, it will not be assigned a value for @id, so the final @id is still the initial value-1

Now let's change the code to the following, assigning the aggregate value after the query result sum to the variable @id

1 Declare @id int=-1;2 3 Select @id=sum(ID) from 4 (5 Select 1  asID6 Union  All 7 Select 2  asID8 Union  All 9 Select 3  asIDTen) asT One where 1<>1 A  - Select @id

This time the result shows that @id is null, and the reason is simple because the query on line 3rd returns only one of the behavior null records, so the @id is assigned one time, so the last @id in a 13-row query is shown as null

So when assigning a value to a SQL variable using the SELECT statement, it is important to understand that the value of the variable depends on the query result of the SELECT statement, and the SELECT statement does not assign a value to the variable if the last row of data for the SELECT statement is not returned. If the SELECT statement eventually returns multiple rows of records, the variable is the value of the last row of records.

An issue to be aware of when assigning variables to a variable using SELECT statements in SQL Server