Digging slightly deeper into this in order to explain this behaviour:
1) Without the parenthesis, this is a simple select query and the server scans the table for all matching entries. It will estimate that one row matches as the search predicate is on a column with an implied unique constraint of the identity property.
No matching rows are found and an empty set ( R{} ) is returned. As the set is empty, no attempt to assign any value to the variable are made, hence it retains its previously assigned value.
2) With the parenthesis, matters become more complicated for the server, as the query is now a subquery. When a subquery is used as an expression or with =, !=, <, <= , > and >= operators, it can only return none or one value. This means that even if the predicate column has an implied unique constraint, the server has first to scan the column, count the matching entries and assert the count, raising an error 512 (Subquery returned more than 1 value) if the row count is greater than one.
The next difference is that the subquery will always bring back a result set of a single row ( R{X} ), the row can either contain a value or an empty (NULL) value representation. When a subquery (inner query) has no outer predicates, it is implemented as a LEFT OUTER JOIN which in the case of no matching values being found, will return as single row with a NULL.
😎
Demonstration
USE tempdb;
GO
IF EXISTS(SELECT OBJECT_ID(N'dbo.TBL_TEST_EMPTY')) DROP TABLE dbo.TBL_TEST_EMPTY;
CREATE TABLE dbo.TBL_TEST_EMPTY
(
TEE_ID INT IDENTITY(1,1) NOT NULL
,TEE_VALUE INT NOT NULL
);
INSERT INTO dbo.TBL_TEST_EMPTY(TEE_VALUE)
VALUES (10),(20),(30),(40),(50),(60);
GO
SET SHOWPLAN_ALL OFF;
GO
DECLARE @XVALUE INT = 100;
SELECT @XVALUE AS RES_START;
/* This query returns an empty set as
there isn't any rows matching the
filter value in the table.
No assignment of value is made and
the variable retains its previous
value.
*/
SELECT
@XVALUE = TEE_VALUE
FROM dbo.TBL_TEST_EMPTY TE
WHERE TE.TEE_ID = 10;
SELECT @XVALUE AS RES_ONE;
/* The first query again without the
variable
*/
SELECT
TEE_VALUE AS EMPTY_WHEN_NOT_MATCHED
FROM dbo.TBL_TEST_EMPTY TE
WHERE TE.TEE_ID = 10;
/* In this case, an entry matches the
filter and the variable is assigned
a new value
*/
SELECT
@XVALUE = TEE_VALUE
FROM dbo.TBL_TEST_EMPTY TE
WHERE TE.TEE_ID = 1;
SELECT @XVALUE AS RES_TWO;
/* This query returns a NULL value
which is assigned to the variable
*/
SELECT
@XVALUE = (
SELECT
TEE_VALUE
FROM dbo.TBL_TEST_EMPTY TE
WHERE TE.TEE_ID = 10
) ;
SELECT @XVALUE AS RES_THREE;
/* The last query again without the
variable
*/
SELECT
(
SELECT
TEE_VALUE
FROM dbo.TBL_TEST_EMPTY TE
WHERE TE.TEE_ID = 10
) AS NULL_WHEN_NOT_MATCHED;
Results
RES_START
-----------
100
RES_ONE
-----------
100
EMPTY_WHEN_NOT_MATCHED
----------------------
RES_TWO
-----------
10
RES_THREE
-----------
NULL
NULL_WHEN_NOT_MATCHED
---------------------
NULL