July 14, 2015 at 7:12 am
Thank you for the good question.
July 14, 2015 at 7:29 am
Nice question, good 2 know, thanx.
Thanks & Best Regards,
Hany Helmy
SQL Server Database Consultant
July 14, 2015 at 8:06 am
The explanation says
When assigning from a query and the query returns no result, SET will assign a NULL value to the variable.
When assigning from a query and the query returns no result, SELECT will not make the assignment and therefore not change the value of the variable.
This is not true.
If you change the first statement to
SELECT @a = ( SELECT
Value
FROM
#tmpPrice
);
then the result is the same, whereas the above explanation would tell you that it should leave it as 0.
The actual reason for the difference is that the first statement alwayssets the value of @a - the only thing the subselect does is to find the value it is going to set it to. If the subselect returns no data, then we don't know what the value should be, hence Null.
The second statement is the other way round - it selects from #tmpPrice and uses the result set to populate @B. Since there is no result set, then it doesn't attempt to populate @b-2 at all, so it keeps its initial value.
July 14, 2015 at 8:06 am
Great question, and a good one to look out for
July 14, 2015 at 8:28 am
TomThomson (7/14/2015)
Hugo Kornelis (7/14/2015)
Simon Jackson (7/14/2015)
The answer is right, but I'm not convinced about the reasoning.If I replace the SET with SELECT , I get the same result.
Just from experimenting, it seems to do with what the the sub query in brackets is returning.
e.g.
SELECT @a = (SELECT 1 WHERE 1=2) -- will assign NULL
SELECT @a = (SELECT 1) -- will assign 1
Thoughts anyone?
Your first example is more convoluted than what this question covers. (And, frankly, more convoluted than what I normally see when I get called in at a customer).
Most people use either
SELECT @a = x FROM SomeTable WHERE 1 = 2;or
SET @a = (SELECT x FROM SomeTable WHERE 1 = 2);Here, the SELECT will not change @a and the SET will set it to NULL.
Also, if the WHERE clause would return multiple rows, the SET will fail with an error (alerting you to a probably incorrect WHERE clause), whereas the SELECT will simply select one of the rows that was returned and use that value, without any alert that this happened.
This seems to me to be a very weird way to describe something very simple (as does the explanation in the QotD). This is nothing to do with the set versus select distinction, but rather to do with whether a select statement is placed in brackets (so that the expression represents a scalar value, which of course will be NULL if the select returns nothing but will throw an error if the rowset has more than one row or more than one column) or not (so that the expression represents a rowset which can validly indicate the absence of a scalar value so that no assignment is to be made by being empty or offer several scalar values for teh optimiser to choose from by returning more than one single column row or cause an error since the rowset offers no scalar value if there are multiple columns).
You, and several other posters, are confusing the outermost statement with subqueries and other trivialities.
The pure basic is the comparison of these constructions:
SET @a = something;
SELECT @a = something [FROM source] [WHERE condition];
In all the cases, something can be a constant, or an expression. If the expression is a subquery, it has to be a single column, and at run time must evaluate to 0 or 1 row.
The SET version will always assign something to @a.
For the SELECT version, it depends on how many rows you would get if you remove "@a =" - if you get 0, no assignment is done; if it's 1, the assignment is done, and if it's more than one, then logically(*) all assignments are done but obviously just one sticks. Which one will stick cannot be predicted.
(*) The optimizer may recognize that it can just pick a single row and then stop working. This is not guaranteed, though.
July 14, 2015 at 8:29 am
Was an easy One. Scored Max.
Thanks.
July 14, 2015 at 11:58 am
Hugo Kornelis (7/14/2015)
TomThomson (7/14/2015)
Hugo Kornelis (7/14/2015)
Simon Jackson (7/14/2015)
The answer is right, but I'm not convinced about the reasoning.If I replace the SET with SELECT , I get the same result.
Just from experimenting, it seems to do with what the the sub query in brackets is returning.
e.g.
SELECT @a = (SELECT 1 WHERE 1=2) -- will assign NULL
SELECT @a = (SELECT 1) -- will assign 1
Thoughts anyone?
Your first example is more convoluted than what this question covers. (And, frankly, more convoluted than what I normally see when I get called in at a customer).
Most people use either
SELECT @a = x FROM SomeTable WHERE 1 = 2;or
SET @a = (SELECT x FROM SomeTable WHERE 1 = 2);Here, the SELECT will not change @a and the SET will set it to NULL.
Also, if the WHERE clause would return multiple rows, the SET will fail with an error (alerting you to a probably incorrect WHERE clause), whereas the SELECT will simply select one of the rows that was returned and use that value, without any alert that this happened.
This seems to me to be a very weird way to describe something very simple (as does the explanation in the QotD). This is nothing to do with the set versus select distinction, but rather to do with whether a select statement is placed in brackets (so that the expression represents a scalar value, which of course will be NULL if the select returns nothing but will throw an error if the rowset has more than one row or more than one column) or not (so that the expression represents a rowset which can validly indicate the absence of a scalar value so that no assignment is to be made by being empty or offer several scalar values for teh optimiser to choose from by returning more than one single column row or cause an error since the rowset offers no scalar value if there are multiple columns).
You, and several other posters, are confusing the outermost statement with subqueries and other trivialities.
The pure basic is the comparison of these constructions:
SET @a = something;SELECT @a = something [FROM source] [WHERE condition];
In all the cases, something can be a constant, or an expression. If the expression is a subquery, it has to be a single column, and at run time must evaluate to 0 or 1 row.
The SET version will always assign something to @a.
For the SELECT version, it depends on how many rows you would get if you remove "@a =" - if you get 0, no assignment is done; if it's 1, the assignment is done, and if it's more than one, then logically(*) all assignments are done but obviously just one sticks. Which one will stick cannot be predicted.
(*) The optimizer may recognize that it can just pick a single row and then stop working. This is not guaranteed, though.
Yes, you are right, I wasn't thinking straight (actually with the interruptions and nonsense going on around me at the time I suspect I wasn't thinking at all). The issue is whether the assignment is included in the select (in which case it will assign one of the values for the specified column if any rows are returned - probably the last one retrieved/calculated but of course an order by clause doesn't determine retrieval order or calculation order so may not influence the value returned - and do nothing if none are returned) or has the result of a subquery assigned to it (in which case if the subquery returns nothing NULL is assigned and if it returns mutiple rows or multiple columns or both and error is returned.
Tom
July 16, 2015 at 4:02 am
Good explanation, especially the one of Hugo.
July 20, 2015 at 2:29 pm
I've ran into this a long time ago, it was painful to solve. Great question.
July 21, 2015 at 6:10 am
Tricky question
Sorry I didn't understand it clearly.
Why both of below queries give Null Values.
DECLARE @a INT=0;
DECLARE @b-2 INT=0;
SET @a = (select 1 where 1=2);
SELECT @b-2 = (select 1 where 1=2);
Go;
DECLARE @a INT=0;
DECLARE @b-2 INT=0;
SET @a = null
SELECT @b-2 = null
About second query as I am setting Null value to variable so it gives me same, but what about first query it should behave like query in the question.
Any quick thought on it or link to document that illustrate it.
July 21, 2015 at 6:26 am
vdkolekar (7/21/2015)
Why both of below queries give Null Values.
Because as I noted earlier on, the explanation is wrong is claiming that the difference in behaviour is between Set and Select. There is no difference between the two (apart from the obvious that you can't do Set... From)
July 21, 2015 at 6:59 am
vdkolekar (7/21/2015)
Tricky questionSorry I didn't understand it clearly.
Why both of below queries give Null Values.
DECLARE @a INT=0;
DECLARE @b-2 INT=0;
SET @a = (select 1 where 1=2);
SELECT @b-2 = (select 1 where 1=2);
Go;
DECLARE @a INT=0;
DECLARE @b-2 INT=0;
SET @a = null
SELECT @b-2 = null
About second query as I am setting Null value to variable so it gives me same, but what about first query it should behave like query in the question.
Any quick thought on it or link to document that illustrate it.
See my post of July 14 in this discussion.
July 25, 2015 at 6:52 pm
vdkolekar (7/21/2015)
Tricky questionSorry I didn't understand it clearly.
Why both of below queries give Null Values.
DECLARE @a INT=0;
DECLARE @b-2 INT=0;
SET @a = (select 1 where 1=2);
The subquery here returns an empty result set. To pass an empty result set into a scalar value, SQL Server translates it as NULL.
SELECT @b-2 = (select 1 where 1=2);
Here you're doing exactly the same thing, except using SELECT as the assignment command. Because you're still doing a scalar assignment, SQL Server translates the empty result set as NULL again.
To get a result like in the original question, you need to have the WHERE clause in the same SELECT as the assignment:
SELECT @b-2 = 1 WHERE 1=2;
This will tell SQL Server to perform the assignment as part of the result set for a SELECT which returns no rows. No rows means no processing of the result set, hence no assignment.
Since SET is purely a scalar operation, it doesn't have the option of having an empty result set, so it always assign something. SELECT will operate the same way when there is no FROM and/or WHERE clause to cause it to return 0 or more than 1 rows of results, which is why your SELECT assigns NULL to @B. Any clause which causes a SELECT to have 0 rows in its result set will result in no assignment, any clause which causes the SELECT to have more than 1 row in its result set will result in the assignment assigning the value from one of the rows (which row's value gets assigned is undefined.)
Viewing 13 posts - 16 through 27 (of 27 total)
You must be logged in to reply to this topic. Login to reply