SET vs. SELECT

Question

Post reply

SET vs. SELECT

sergey.gigoyan

Ten Centuries

Points: 1213
More actions
July 14, 2015 at 12:21 am

#398738

Comments posted to this topic are about the item SET vs. SELECT
July 14, 2015 at 3:05 am

This was removed by the editor as SPAM

Viewing 15 posts - 1 through 15 (of 27 total)

You must be logged in to reply to this topic. Login to reply

Umer Akram SSC-Insane Points: 22209 More actions · Answer 1

very nice question and good knowledge base. Thanks for sharing

Carlo Romagnano SSC-Insane Points: 22597 More actions · Answer 2

Carlo Romagnano

SSC-Insane

Points: 22597

July 14, 2015 at 1:04 am

#1812212

Easy, thanks!

😀

Koen Verbeeck SSC Guru Points: 259085 More actions · Answer 3

Very good to know. Thanks for the question.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Emil B SSCertifiable Points: 5568 More actions · Answer 4

Emil B

SSCertifiable

Points: 5568

July 14, 2015 at 2:26 am

#1812224

Very good question!

Simon Jackson SSC Rookie Points: 39 More actions · Answer 5

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?

Hugo Kornelis SSC Guru Points: 64780 More actions · Answer 6

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.

Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

wedneyyuri SSC Veteran Points: 234 More actions · Answer 7

Good explanation, I never had this kind of problem.

wedneyyuri SSC Veteran Points: 234 More actions · Answer 8

Good explanation, I never had this kind of problem.

sknox SSChampion Points: 12388 More actions · Answer 9

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?

It's not what the subquery returns, but if the subquery returns anything.

When you place a subquery in a scalar expression you're requiring that SQL Server convert a result set into a scalar value.

If there's a single result, no problem.

If there are multiple results, SQL Server fails with an error.

If there's no result, SQL Server returns NULL.

However, if you place a WHERE clause that eliminates all rows in a SELECT statement, SQL does not return any values. Since the assignment is part of the SELECT clause, it does not happen.

Consider:

SELECT @a = (SELECT 1 WHERE 1=2) -- will assign NULL

SELECT @a = (SELECT 1) WHERE 1=2 -- will not assign a value

In short, it's not SET vs SELECT, but sets vs scalars and judicious placement of your WHERE clause.

Ed Wagner SSC Guru Points: 287024 More actions · Answer 10

Excellent question. Thanks.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

Simon Jackson SSC Rookie Points: 39 More actions · Answer 11

(Posted this before seeing the other answer above. Yes you have put it succinctly.)

Hi,

Sorry, I was just trying to use the minimum code.

So with code:

DECLARE @SomeTable TABLE (x int)

DECLARE @a int = 0;

DECLARE @b-2 INT = 0;

SELECT @a = x FROM @SomeTable -- 1.

SET @b-2 = (SELECT x FROM @SomeTable) -- 2.

SELECT @b-2 = (SELECT x FROM @SomeTable) -- 3.

SELECT @a as a, @b-2 as b

Lines marked 2 & 3 give identical results. The reason is that when you do the set/select from a subquery, you get one row returned which is null.

Try:

select (select x from @SomeTable);vsselect x from @SomeTable;

In the latter, no rows are returned because the table is obviously empty.

So the difference is not the use of set/select, it is that the underlying query is returning no rows in the first case but a single null row in the second.

webrunner SSC-Dedicated Points: 31713 More actions · Answer 12

Great question, thanks!

- webrunner

-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

TomThomson SSC Guru Points: 104773 More actions · Answer 13

edit: Rubbish deleted.

I seem to writing nonsense today - must remember to switch brain on before commenting on anything. Or possibly just not try thinking about SQL at same time as listening listening to wife twittering incomprehensible nonsense about something utterly meaningless, since that listening requires brain to be off.

Tom