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


UNION


UNION

Author
Message
steve.jacobs
steve.jacobs
Right there with Babe
Right there with Babe (760 reputation)Right there with Babe (760 reputation)Right there with Babe (760 reputation)Right there with Babe (760 reputation)Right there with Babe (760 reputation)Right there with Babe (760 reputation)Right there with Babe (760 reputation)Right there with Babe (760 reputation)

Group: General Forum Members
Points: 760 Visits: 279
Comments posted to this topic are about the item UNION
bitbucket-25253
bitbucket-25253
SSCrazy Eights
SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)

Group: General Forum Members
Points: 9429 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
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

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

Hope this helps...

Ford Fairlane
Rock and Roll Detective





Paul White
Paul White
SSC-Insane
SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)

Group: General Forum Members
Points: 20046 Visits: 11359
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 (571 views, 26.00 KB)
twin.devil
twin.devil
Hall of Fame
Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)

Group: General Forum Members
Points: 3041 Visits: 2684

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! (901 reputation)SSC Eights! (901 reputation)SSC Eights! (901 reputation)SSC Eights! (901 reputation)SSC Eights! (901 reputation)SSC Eights! (901 reputation)SSC Eights! (901 reputation)SSC Eights! (901 reputation)

Group: General Forum Members
Points: 901 Visits: 135
Agree with Paul

without table definitions, it was difficult to make an informed decision.
Hany Helmy
Hany Helmy
Hall of Fame
Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)

Group: General Forum Members
Points: 3487 Visits: 1117
Nice & easy; thanx.
martin.whitton
martin.whitton
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1432 Visits: 1774
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
SSCarpal Tunnel
SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)

Group: General Forum Members
Points: 4813 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
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1237 Visits: 1362
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