March 8, 2012 at 2:56 am
R.P.Rozema (3/8/2012)
To finalize this: My explanation is wrong and my question + answer was correct only by luck.The proper explanation has been given in this thread. To be sure I am getting it now I'll try to summarize it:
Union queries are interpreted left to right. If "union all" is followed by "union", the "union all" will return duplicates, but these will be filtered by the following "union". Other way around, if "union" is followed by "union all", any duplicates from the first 2 statements are filtered, but new duplicates may be introduced by the following "union all".
Parenthesis can be used to override the left-to-right evaluation.
An illustration can be given by putting more rows in the test tables:
create table #t1 (col int not null);create table #t2 (col int not null);
create table #t3 (col int not null);
insert #t1 (col) values(1), (1);
insert #t2 (col) values(2), (2);
insert #t3 (col) values(3), (3);
select col from #t1
UNION
select col from #t2
UNION ALL
select col from #t3;
select col from #t1
UNION ALL
select col from #t2
UNION
select col from #t3;
And now the results are:
col-----------
1
2
3
3
(4 row(s) affected)
col
-----------
1
2
3
(3 row(s) affected)
Seems like I was the first to learn something from my own question :).
Thanks for all the feedback!
OK!
March 8, 2012 at 4:26 am
good question!
March 8, 2012 at 5:11 am
R.P.Rozema (3/8/2012)
To finalize this: My explanation is wrong and my question + answer was correct only by luck....
And why is it a great question?
Why UNION (ALL) is basic but very confusing and anyone can be tricked by it.
Also it remember us to be meticulous with precedence.
Thanks Rozema.
March 8, 2012 at 6:16 am
Nice.
(from next time rather converting to image, it will be better to paste the exact SQL)
ww; Raghu
--
The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.
March 8, 2012 at 6:36 am
Raghavendra Mudugal (3/8/2012)
(from next time rather converting to image, it will be better to paste the exact SQL)
Actually no. The code was intentionally presented as a picture for 2 reasons:
1 - cheaters will have to type the entire challenge when they try to find the correct answer by simply running it, and
2 - presented as a picture you'll have my syntax highlighting to aid you in reading the code correctly.
March 8, 2012 at 6:48 am
R.P.Rozema (3/8/2012)
Raghavendra Mudugal (3/8/2012)
(from next time rather converting to image, it will be better to paste the exact SQL)Actually no. The code was intentionally presented as a picture for 2 reasons:
1 - cheaters will have to type the entire challenge when they try to find the correct answer by simply running it, and
2 - presented as a picture you'll have my syntax highlighting to aid you in reading the code correctly.
I will type the complete code only if the SQL code has interesting thing to learn, just inserting in to temp and using couple of unions - either typing or copy/pasting will not make any sense - as long as the complete focus is on the UNION/ALL feature.
Typing is always fun when something to "type" interesting.
anyways its your question and its your method of posting... 😉
ww; Raghu
--
The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.
March 8, 2012 at 7:15 am
Nice back to basics question. Keep them coming!!!
_______________________________________________________________
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/
March 8, 2012 at 7:47 am
create table #t1( col int not null);
insert #t1(col) values(1),(1),(2),(3),(3)
select col from #t1
union
select col from #t1
union all
select col from #t1
select col from #t1
union all
select col from #t1
union
select col from #t1
select col from #t1
union all
(
select col from #t1
union
select col from #t1
)
drop table #t1
This is why I got the question wrong, I only used one table, I didn't think about order of table.
Order of table is exactly why this union is acting the way it did for the answer.
Great challenging post! 🙂
March 8, 2012 at 7:50 am
Great question. The Union will eliminate the duplicates as has been demonstrated. Thanks.
March 8, 2012 at 8:28 am
Very nice. Thanks for the question!
Chris Umbaugh
Data Warehouse / Business Intelligence Consultant
twitter @ToledoSQL
March 8, 2012 at 8:42 am
Nice easy question. Still got me to go dig into BOL to make sure I had the right answer before I clicked submit though, so thank you!
March 8, 2012 at 8:45 am
March 8, 2012 at 8:48 am
Thanks for the question.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 8, 2012 at 10:19 am
Thanks for a good question and a great discussion.
Once again, the lesson is to pay attention to all the details....
Rob Schripsema
Propack, Inc.
Viewing 15 posts - 16 through 30 (of 49 total)
You must be logged in to reply to this topic. Login to reply