Performing union data with null values

Question

Performing union data with null values

Junior Galvão - MVP

SSCertifiable

Points: 6934
More actions
November 14, 2016 at 8:49 pm

#318932

Comments posted to this topic are about the item Performing union data with null values
November 14, 2016 at 10:17 pm

This was removed by the editor as SPAM

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

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

Carlo Romagnano SSC-Insane Points: 22672 More actions · Answer 1

Stewart "Arturius" Campbell (11/14/2016)
Interesting question, thanks Junior.
However, as I am sure you are aware, the sequence is important:
if the query is changed toSELECT 0
UNION
SELECT 1
UNION
SELECT NULL
UNION ALL
SELECT NULL
UNION
SELECT 2;, the result is very different, i.e.
NULL
0
1
2

Also, you can change the "precedence" with brackets:

SELECT 0

UNION

SELECT 1

UNION

SELECT 2

UNION

(

SELECT NULL

UNION ALL

SELECT NULL

)

Toreador SSChampion Points: 11366 More actions · Answer 2

The answer is right, but the explanation is wrong, as it says the answer is 0,1,2,NULL,NULL

If that had been one of the options I might have got it wrong as I couldn't remember whether NULLs were sorted first or last. I suspect that was supposed to be option 2 but a copy-and-paste error added the third Null to the start?

Ed Wagner SSC Guru Points: 287026 More actions · Answer 3

Nice question to reinforce a concept. Thanks, Junior.

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

sknox SSChampion Points: 12388 More actions · Answer 4

Toreador (11/15/2016)
The answer is right, but the explanation is wrong, as it says the answer is 0,1,2,NULL,NULL
If that had been one of the options I might have got it wrong as I couldn't remember whether NULLs were sorted first or last. I suspect that was supposed to be option 2 but a copy-and-paste error added the third Null to the start?

Technically, 0,1,2,NULL,NULL and NULL,0,1,2,NULL should both be considered correct. There is nothing in the SQL of the question which enforces an order.

The order we do see, NULL,0,1,2,NULL, comes as the effect of the UNION enforcing uniqueness via an algorithm which also sorts the results.

However, that is just the way SQL Server happens to do it; it is not required behavior. In future, they may change the uniqueness algorithm to use some other method.

Budd Hall of Fame Points: 3781 More actions · Answer 5

Interesting Question, but you can't say that

NULL

0

1

2

NULL

is a wrong answer.

I tested this using SSMS 2016, and 2008R2 and both provided the same order of results. Perhaps the results are dependent upon the Query settings ?

Ed Wagner SSC Guru Points: 287026 More actions · Answer 6

Budd (11/15/2016)
Interesting Question, but you can't say that
NULL
0
1
2
NULL
is a wrong answer.
I tested this using SSMS 2016, and 2008R2 and both provided the same order of results. Perhaps the results are dependent upon the Query settings ?

The answer is correct. Because there's no ORDER BY clause, there's no guarantee of the order of the results.

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

Luis Cazares SSC Guru Points: 183706 More actions · Answer 7

The answer is correct but the explanation seems to be missing the part where it explains why is the second NULL not counted as duplicate. Or maybe it's just me expecting the possibility that UNION and UNION ALL might have different precedence when they don't.

It would be nice if there were an explanation on how UNION (ALL) work from left to right (top to bottom), so the last UNION ALL won't be affected by the removal of duplicates from the previous UNION. This is shown on example D from the UNION page on BOL, but I feel that it's explained better on the EXCEPT and INTERSECT page.

If EXCEPT or INTERSECT is used together with other operators in an expression, it is evaluated in the context of the following precedence:
1. Expressions in parentheses
2. The INTERSECT operand
3. EXCEPT and UNION evaluated from left to right based on their position in the expression
If EXCEPT or INTERSECT is used to compare more than two sets of queries, data type conversion is determined by comparing two queries at a time, and following the previously mentioned rules of expression evaluation.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Budd Hall of Fame Points: 3781 More actions · Answer 8

While an ORDER BY clause will guarantee an order of results, I thought I read somewhere (long ago) that with out ORDER BY, the order or results can be influenced (not guaranteed) by other factors such as entry order, and query results settings. Such that the results could be the same (something like) 80% of the time.

I think I read something like that but I could have been hallucinating. 🙂

:w00t:

akljfhnlaflkj SSC Guru Points: 76202 More actions · Answer 9

akljfhnlaflkj

SSC Guru

Points: 76202

November 15, 2016 at 7:14 am

#1912494

Nice question, thanks.

Sean Lange SSC Guru Points: 286590 More actions · Answer 10

Budd (11/15/2016)
While an ORDER BY clause will guarantee an order of results, I thought I read somewhere (long ago) that with out ORDER BY, the order or results can be influenced (not guaranteed) by other factors such as entry order, and query results settings. Such that the results could be the same (something like) 80% of the time.
I think I read something like that but I could have been hallucinating. 🙂
:w00t:

They will be influenced by things like the clustered index...up to a point. But influenced is NOT the same thing as knowing what order they will be returned. When you have no order by the order returned is the order that is fastest for the query engine to assemble them. This means the clustered index is very likely for a while but at some point it will stop working. Here is a great article on this topic. http://blogs.msdn.com/b/conor_cunningham_msft/archive/2008/08/27/no-seatbelt-expecting-order-without-order-by.aspx

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/