Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


UNION


UNION

Author
Message
steve.jacobs
steve.jacobs
SSChasing Mays
SSChasing Mays (644 reputation)SSChasing Mays (644 reputation)SSChasing Mays (644 reputation)SSChasing Mays (644 reputation)SSChasing Mays (644 reputation)SSChasing Mays (644 reputation)SSChasing Mays (644 reputation)SSChasing Mays (644 reputation)

Group: General Forum Members
Points: 644 Visits: 279
Comments posted to this topic are about the item UNION
bitbucket-25253
bitbucket-25253
SSCertifiable
SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)

Group: General Forum Members
Points: 6221 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
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

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

Hope this helps...

Ford Fairlane
Rock and Roll Detective





Paul White
Paul White
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11238 Visits: 11354
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 (565 views, 26.00 KB)
twin.devil
twin.devil
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1557 Visits: 2624

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


Its a boobby trap ... :-)
SADSAC
SADSAC
SSC Eights!
SSC Eights! (814 reputation)SSC Eights! (814 reputation)SSC Eights! (814 reputation)SSC Eights! (814 reputation)SSC Eights! (814 reputation)SSC Eights! (814 reputation)SSC Eights! (814 reputation)SSC Eights! (814 reputation)

Group: General Forum Members
Points: 814 Visits: 132
Agree with Paul

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

Group: General Forum Members
Points: 2618 Visits: 1108
Nice & easy; thanx.
martin.whitton
martin.whitton
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1317 Visits: 1767
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
Hall of Fame
Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)

Group: General Forum Members
Points: 3811 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
SSC Eights!
SSC Eights! (934 reputation)SSC Eights! (934 reputation)SSC Eights! (934 reputation)SSC Eights! (934 reputation)SSC Eights! (934 reputation)SSC Eights! (934 reputation)SSC Eights! (934 reputation)SSC Eights! (934 reputation)

Group: General Forum Members
Points: 934 Visits: 1347
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