October 11, 2014 at 12:39 pm
Dear Readers,
I can't understand why I get 2 different results on running with a Bracket I get 'NULL' and without a bracket I get the declared variable value which is 'Noname'
Below is Query 1:
Declare @testvar char(20)
Set @testvar = 'noname'
Select @testvar= pub_name
FROM publishers
WHERE pub_id= '999'
Select @testvar
Out put of this query is 'Noname'
BUT when I type the same query in the following manner I get Null-------Please note that the only difference between this query below is I used brackets and Select in the Select@testvar statement
Declare @testvar char(20)
Set @testvar = 'noname'
Select @testvar=(Select pub_name
FROM publishers
WHERE pub_id= '999')
Select @testvar
Thank-you
October 11, 2014 at 12:46 pm
Quick thought, the former returns an empty set and no assignment takes place, the latter returns a set with a empty (null) column whitch is assigned to the variable.
😎
October 11, 2014 at 12:55 pm
But assigned to the variable is 'NoName' not a NULL
October 11, 2014 at 1:02 pm
Eirikur's correct.
the latter returns a set with a empty (null) column whitch is assigned to the variable.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 11, 2014 at 11:28 pm
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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply