SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


UNION


UNION

Author
Message
steve.jacobs
steve.jacobs
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1014 Visits: 279
Comments posted to this topic are about the item UNION
bitbucket-25253
bitbucket-25253
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24123 Visits: 25280
Nice QOD to start the week ---- Thanks

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read

Before posting a performance problem please read
Ford Fairlane
Ford Fairlane
SSCarpal Tunnel
SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)

Group: General Forum Members
Points: 4042 Visits: 836
Too easy and straight forward QotD. :-)

Hope this helps...

Ford Fairlane
Rock and Roll Detective





Paul White
Paul White
SSC Guru
SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)

Group: General Forum Members
Points: 54422 Visits: 11391
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:





Paul White
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
Attachments
ssc.png (574 views, 26.00 KB)
twin.devil
twin.devil
SSCrazy Eights
SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)

Group: General Forum Members
Points: 8332 Visits: 2737

That produces the following plan featuring a Merge Join (my answer!) running in Union mode:


Its a boobby trap ... :-)
SADSAC
SADSAC
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1089 Visits: 139
Agree with Paul

without table definitions, it was difficult to make an informed decision.
Hany Helmy
Hany Helmy
SSCertifiable
SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)

Group: General Forum Members
Points: 5617 Visits: 1117
Nice & easy; thanx.
martin.whitton
martin.whitton
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1775 Visits: 1787
Stewart Arthur Campbell (11/4/2013)
Agree with Paul

without 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".
sqlnaive
sqlnaive
SSCrazy Eights
SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)

Group: General Forum Members
Points: 8261 Visits: 2774
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
sqldoubleg
sqldoubleg
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1707 Visits: 1363
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.

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search