UNION
-
Comments posted to this topic are about the item UNION
-
Too easy and straight forward QotD. 🙂
Hope this helps...
Ford Fairlane
Rock and Roll Detective
-
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:
-
That produces the following plan featuring a Merge Join (my answer!) running in Union mode:
Its a boobby trap ... 🙂
-
Agree with Paul
without table definitions, it was difficult to make an informed decision.
-
Nice & easy; thanx.
Thanks & Best Regards,
Hany Helmy
SQL Server Database Consultant -
Stewart Arthur Campbell (11/4/2013)
Agree with Paulwithout table definitions, it was difficult to make an informed decision.
Without table definitions it's impossible to make an informed decision!
From my testing, it seems that if there is an index on "city" in the "contacts" table, then the execution plan will feature a merge join (if there is only an index on "city" in the "contacts2" table it will not).
So, from the information given in the question, all you can say is that the answer is either "merge join" OR "none of the above".
-
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:
I fell for the same. :angry:
-
Thanks for the question,
Thankfully for me this was a theoretical question at an upper level, not to dig into the query plan to see what the query processor does!
As per BOL
The UNION operation is different from using joins that combine columns from two tables.
-
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.
-
As well as the answer being wrong because a join may be used to perform the union, the explanation is wrong even when there is no join. It is unlikely that there will be a "unique sort", because there is no need for one: the union can (and nearly always will) be achieved by a hash merge; there is of course no concatenation - the two bunches of values might be concatenated if UNION ALL were used but it would be strange (to say the lest) to do that for UNION without ALL. There will be a final sort for the ORDER BY 1 clause - which brings up another point - the code layout is awful, it looks as if the writer thinks that the ORDER BY clause is part of the second select statement instead applying to the union of the two select statements' results.
Since "It Depends" wasn't available as an answer, I plumped for the easy answer on the theory that is the question's author was aware that their might be a join in there the question would have been rather different - an easy point.
Tom
-
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.
-
I thought it was a good QOTD, though looking at the comments not to may agree.
-
Liked that it made me think. Not thrilled with the explanation. Got it right in the end, but fall into the category of "It depends" being a better answer.
Thanks for posting the question Steve.
[font="Verdana"]Please don't go. The drones need you. They look up to you.[/font]
Connect to me on LinkedIn
Viewing 15 posts - 1 through 15 (of 36 total)
You must be logged in to reply to this topic. Login to reply