Behavior of TOP with WHERE clause . Why is this query returning a value instead of NULL?-CodePudding

I'm trying to make sense of the following behavior. Consider this T-SQL query:

DECLARE @num table (n tinyint NOT NULL)

INSERT INTO @num VALUES (1), (2), (3)

DECLARE @iNum tinyint

SELECT  
    TOP 1 @iNum = n
FROM
    @num
ORDER BY
    n ASC

SELECT @iNum

SELECT 
    TOP 1 @iNum  = n
FROM
    @num
WHERE
    n > @iNum
ORDER By
    n ASC
       
SELECT @iNum

SELECT 
    TOP 1 @iNum  = n
FROM
    @num
WHERE
    n > @iNum
ORDER By
    n ASC
        
SELECT @iNum

SELECT 
    TOP 1 @iNum  = n
FROM
    @num
WHERE
    n > @iNum
ORDER By
    n ASC
        
SELECT @iNum

I am trying to use a loop that goes through ascending integral values in a table. I ditched the loop and made explicit selects for the sake of this post, but the phenomenon is the same in both setups.

I would expect this to print/return 1,2,3, NULL. But instead get 1,2,3,3. Any idea why this is?

CodePudding user response：

The value of @iNum is 3 at the end because that was the last value that was actually assigned to the variable.

The last query that assigns a value, due to the where clause, returns no rows, it does not result in a row with a NULL value.

If you remove the assignment from the last query and run it, you'll see - nothing gets assigned in the last query because no rows are returned.

For additional clarity - if the variable assignment was the result of an expression, it would be assigned a NULL as that would be the expression's result if the query returned no rows, eg

set @variable = (<query>);