UNION

  • steve.jacobs

    SSCommitted

    Points: 1830

    Comments posted to this topic are about the item UNION

  • Ron McCullough

    SSC Guru

    Points: 63877

    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[/url]
    Before posting a performance problem please read[/url]

  • Ford Fairlane

    SSCertifiable

    Points: 7664

    Too easy and straight forward QotD. 🙂

    Hope this helps...

    Ford Fairlane
    Rock and Roll Detective

  • Paul White

    SSC Guru

    Points: 150341

    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:

  • twin.devil

    SSC-Insane

    Points: 22208

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

    Its a boobby trap ... 🙂

  • SADSAC

    SSCommitted

    Points: 1870

    Agree with Paul

    without table definitions, it was difficult to make an informed decision.

  • Hany Helmy

    SSChampion

    Points: 13291

    Nice & easy; thanx.

  • martin.whitton

    SSCrazy

    Points: 2560

    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

    SSCoach

    Points: 17435

    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

    Hall of Fame

    Points: 3090

    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.

  • Richard Warr

    SSCertifiable

    Points: 6955

    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.

    _____________________________________________________________________
    MCSA SQL Server 2012

  • TomThomson

    SSC Guru

    Points: 104707

    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

  • steve.jacobs

    SSCommitted

    Points: 1830

    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.

  • paul s-306273

    SSChampion

    Points: 10526

    I thought it was a good QOTD, though looking at the comments not to may agree.

  • Thomas Abraham

    SSChampion

    Points: 10761

    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 37 total)

You must be logged in to reply to this topic. Login to reply