UNION
-
Excellent Question, I thought it was full outer join, but I was wrong and learned something! I have obvioulsy never read about UNION in BOL until now!
-
Paul White (11/3/2013)
The above query does not produce a join. It produces a distinct sort then concatenation of values from one table and the other.
Well it might do, or it might do something else. Without table definitions and data, how could we know?
CREATE TABLE Contacts
(
City nvarchar(25) NOT NULL
)
CREATE TABLE Contacts2
(
City nvarchar(25) NOT NULL
)
CREATE NONCLUSTERED INDEX i ON dbo.Contacts (City);
CREATE NONCLUSTERED INDEX i2 ON dbo.Contacts (City);
UPDATE STATISTICS dbo.Contacts WITH ROWCOUNT = 10000, PAGECOUNT = 1000;
UPDATE STATISTICS dbo.Contacts2 WITH ROWCOUNT = 10000, PAGECOUNT = 1000;
SELECT city
FROM contacts
UNION
SELECT city
FROM contacts2
GROUP BY city
ORDER BY 1;
DROP TABLE dbo.Contacts, dbo.Contacts2;
That produces the following plan featuring a Merge Join (my answer!) running in Union mode:
You're right.
the right answer is not deterministic. It depends on other stuff.
Regards
IgorMi
Igor Micev,My blog: www.igormicev.com
-
I guess I've been spending too much time looking at query plans lately. I saw merge join and went right for it and didn't even bother to look at the other answers. Technically a UNION is a merge join, at least at the physical level.
http://technet.microsoft.com/en-us/library/aa178388.aspx
Joshua Perry
http://www.greenarrow.net -
Thought it was a Merge Join too.
-
Paul White (11/3/2013)
The above query does not produce a join. It produces a distinct sort then concatenation of values from one table and the other.
Well it might do, or it might do something else. Without table definitions and data, how could we know?
CREATE TABLE Contacts
(
City nvarchar(25) NOT NULL
)
CREATE TABLE Contacts2
(
City nvarchar(25) NOT NULL
)
CREATE NONCLUSTERED INDEX i ON dbo.Contacts (City);
CREATE NONCLUSTERED INDEX i2 ON dbo.Contacts (City);
UPDATE STATISTICS dbo.Contacts WITH ROWCOUNT = 10000, PAGECOUNT = 1000;
UPDATE STATISTICS dbo.Contacts2 WITH ROWCOUNT = 10000, PAGECOUNT = 1000;
SELECT city
FROM contacts
UNION
SELECT city
FROM contacts2
GROUP BY city
ORDER BY 1;
DROP TABLE dbo.Contacts, dbo.Contacts2;
That produces the following plan featuring a Merge Join (my answer!) running in Union mode:
And in another case, I get a Hash Match running in Union Mode.
Change Paul's code to:
DROP INDEX i ON dbo.Contacts
DROP INDEX i2 ON dbo.Contacts;
UPDATE STATISTICS dbo.Contacts WITH ROWCOUNT = 1000000, PAGECOUNT = 100000;
UPDATE STATISTICS dbo.Contacts2 WITH ROWCOUNT = 1000000, PAGECOUNT = 100000;
And I get
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 -
Richard Warr (11/4/2013)
Seems to be one where the people who didn't think about it found it easy (see some of the comments above) whilst those who dug a bit deeper stood quite a good chance of getting it wrong.Thank you to those in the latter category for the opportunity to learn something new today.
Why should one have to over complicate everything - the question was given was enough information to come up with the correct answer. Sure if we had to look deeper and come up with our own tables we would no doubt prove the given answer wrong, for this and most questions - so sometimes it pays not to think about it as you put it and just answer the simple question.
Hope this helps...
Ford Fairlane
Rock and Roll Detective
-
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 -
Ford Fairlane (11/4/2013)
Richard Warr (11/4/2013)
Seems to be one where the people who didn't think about it found it easy (see some of the comments above) whilst those who dug a bit deeper stood quite a good chance of getting it wrong.Thank you to those in the latter category for the opportunity to learn something new today.
Why should one have to over complicate everything - the question was given was enough information to come up with the correct answer. Sure if we had to look deeper and come up with our own tables we would no doubt prove the given answer wrong, for this and most questions - so sometimes it pays not to think about it as you put it and just answer the simple question.
I hope you don't use that approach when developing software - if you do, you are likely to write rather a lot of rather inadequate code and your comment certainly doesn't engender confidence in your willingness to dagnose and fix any resulting problems.
Tom
-
I am really surprised that only one third of the replies has been correct.
Thanks for the question, Steve!
-
Revenant (11/5/2013)
I am really surprised that only one third of the replies has been correct.Thanks for the question, Steve!
Well, maybe we should say 52% are correct - the 34% who said "none of the above" plus the 28% whio daid "merge join"; if the question had been phrased so that it was about relational theory then only "none of the above" would be correct, but it actuall asks what sort of join you get if you run the query, oresumably given what site we are on that's run it using MS SQL Server and one possibility when you do that is that the optimiser chooses to do a merge join (easily demonstrated, as Paul has pointed out).
Mind you, I reckon that 48% saying neither "none of the above" or "merge join" is pretty surprising, because it must require quite some imagination to dream up a use for, for example, a left outer join to do a simple union like this one.
Tom
-
L' Eomot Inversé (11/5/2013)
Revenant (11/5/2013)
I am really surprised that only one third of the replies has been correct.Thanks for the question, Steve!
Well, maybe we should say 52% are correct - the 34% who said "none of the above" plus the 28% whio daid "merge join"; if the question had been phrased so that it was about relational theory then only "none of the above" would be correct, but it actuall asks what sort of join you get if you run the query, oresumably given what site we are on that's run it using MS SQL Server and one possibility when you do that is that the optimiser chooses to do a merge join (easily demonstrated, as Paul has pointed out).
Mind you, I reckon that 48% saying neither "none of the above" or "merge join" is pretty surprising, because it must require quite some imagination to dream up a use for, for example, a left outer join to do a simple union like this one.
L'Eomot,
Just for you, next time, if, like this one was "theoretical", I will certainly point it out in the description and the explanation. All, my apologies for the "confusion" with this query and the inadequate explanation. For those who missed this, :crying: and for those who did not 😀
-
L' Eomot Inversé (11/5/2013)
Ford Fairlane (11/4/2013)
Richard Warr (11/4/2013)
Seems to be one where the people who didn't think about it found it easy (see some of the comments above) whilst those who dug a bit deeper stood quite a good chance of getting it wrong.Thank you to those in the latter category for the opportunity to learn something new today.
Why should one have to over complicate everything - the question was given was enough information to come up with the correct answer. Sure if we had to look deeper and come up with our own tables we would no doubt prove the given answer wrong, for this and most questions - so sometimes it pays not to think about it as you put it and just answer the simple question.
I hope you don't use that approach when developing software - if you do, you are likely to write rather a lot of rather inadequate code and your comment certainly doesn't engender confidence in your willingness to dagnose and fix any resulting problems.
I certainly don't use that approach when developing software - but for a QotD, given the information that we are, certainly not worth my time to dive into the inner workings of SQL when majority of the time, its the simple solution that is needed.
Hope this helps...
Ford Fairlane
Rock and Roll Detective -
steve.jacobs (11/5/2013)
L'Eomot,Just for you, next time, if, like this one was "theoretical", I will certainly point it out in the description and the explanation. All, my apologies for the "confusion" with this query and the inadequate explanation. For those who missed this, :crying: and for those who did not 😀
Well, if you have learnt something, as well as the at least 48% who have also (I hope) learnt something, it's all good. In my experience it's almost impossible to get a QOTD right, there's always something wrong - So far I've contributed 18 questions, of which 13 have something wrong with them according to the standards I think QOTD should achieve, 1 is too non-technical to count. and 3 are about a topic so obscure that no-one should care what the answer is. That leaves just 1 out of 18 that comes anyhere close to the standard I want from other QOTD contributors, like you - so don't concern yourself if people like me seem to be complaining about your question, we are mostly just trying to ensure that as many people as possible learn from it and we know full well we wouldn't do better ourselves than you have done.
Tom
-
L' Eomot Inversé (11/5/2013)
steve.jacobs (11/5/2013)
L'Eomot,Just for you, next time, if, like this one was "theoretical", I will certainly point it out in the description and the explanation. All, my apologies for the "confusion" with this query and the inadequate explanation. For those who missed this, :crying: and for those who did not 😀
Well, if you have learnt something, as well as the at least 48% who have also (I hope) learnt something, it's all good. In my experience it's almost impossible to get a QOTD right, there's always something wrong - So far I've contributed 18 questions, of which 13 have something wrong with them according to the standards I think QOTD should achieve, 1 is too non-technical to count. and 3 are about a topic so obscure that no-one should care what the answer is. That leaves just 1 out of 18 that comes anyhere close to the standard I want from other QOTD contributors, like you - so don't concern yourself if people like me seem to be complaining about your question, we are mostly just trying to ensure that as many people as possible learn from it and we know full well we wouldn't do better ourselves than you have done.
Being who we are and making our living the way we are, we are forced to - or chose to - learn all the time. I have a highest regard for anyone who contributes a QotD - we all learn from answering it, and the brave soul who contributed it learns from the feedback. (I did, couple of times.)
Let's keep it going.
-
steve.jacobs (11/4/2013)
twin.devil (11/4/2013)
That produces the following plan featuring a Merge Join (my answer!) running in Union mode:
Its a boobby trap ... 🙂
twin.devil, you stated it perfectly, it is a trap question. Sure, I could have put "It Depends" as one of the answers, but looking at the query, setting up two simple tables and then running an execution plan against it, proves there is no join involved. Sure, the query could have been written "better," sure, I could have given you table definitions, sure, I could have not used the "order by clause" and yes, I could have "better explained" the query in the explanation section. However, I did not. Again, this is the type of cr*p I have to deal on daily basis from legacy code. I found this one interesting (as this type of query is part of a larger linked query (over 2000 lines)) because I initially fell for the merge and left outer join traps, but looking in to the query closer, with table definitions, execution plans, et all., the answer was "simple": A distinct selection of items from two almost identical tables.
+1, steve.jacobs ... 🙂
Viewing 15 posts - 16 through 30 (of 36 total)
You must be logged in to reply to this topic. Login to reply