Select unmatched data from two columns

  • Lokesh Vij

    SSChampion

    Points: 10836

    SQL Kiwi (11/26/2012)


    That's the one I wrote first (though I used COALESCE, as it happens). The optimizer's expansion of the full join to left join concat anti-semi join eventually led me to the one that uses UNION, INTERSECT and EXCEPT. I like that combination of operators; it's the set-theoretic idea of difference being the union minus the intersection.

    Perfect! Very rightly said.

    It is very easy to understand those joins when we compare and contrast with set-theory concepts 🙂

    ~ Lokesh Vij


    Guidelines for quicker answers on T-SQL question[/url]
    Guidelines for answers on Performance questions

    Link to my Blog Post --> www.SQLPathy.com[/url]

    Follow me @Twitter

  • paul s-306273

    SSChampion

    Points: 10589

    Interesting qotd.

  • Dana Medley

    SSCertifiable

    Points: 6764

    So many words for a Monday morning question; I ended up reading it 4 or 5 times before answering. :hehe:

    Thank you very much, it was a good one.



    Everything is awesome!

  • Kick6Tiger

    SSCrazy

    Points: 2368

    The oddity that I find interesting about this question is the fact that the UNION automatically sorts the results. In my head I'm thinking the results would be 4,7,2,8 and 2,8,4,7 from the two correct options.

    ***I now see that using the UNION ALL would not do the sorting to look for duplicates and would have given me the results that I expected.

    Aigle de Guerre!

  • (Bob Brown)

    SSCrazy

    Points: 2705

    Great question. Gave me something to think about.

  • Paul White

    SSC Guru

    Points: 150442

    Meow Now (11/26/2012)


    The oddity that I find interesting about this question is the fact that the UNION automatically sorts the results. In my head I'm thinking the results would be 4,7,2,8 and 2,8,4,7 from the two correct options.

    ***I now see that using the UNION ALL would not do the sorting to look for duplicates and would have given me the results that I expected.

    It depends on the physical implementation of the UNION / UNION ALL by the query optimizer. Depending on your SQL Server version, you will / might see different results with e.g. OPTION (HASH UNION) or OPTION (MERGE UNION). As always, ORDER BY is required for output ordering guarantees 🙂

  • Revenant

    SSC-Forever

    Points: 42467

    A headscratcher... but a nice one. Thanks!

  • Ken Wymore

    SSCoach

    Points: 16387

    Took a bit to think over since there were so many lines of code, but a good question anyways. Thanks!

  • asifkareem

    Ten Centuries

    Points: 1089

    Nice One, +1

  • jbwa

    SSC Eights!

    Points: 832

    Hmm. Selected 1 and 3 and was told wrong.

Viewing 10 posts - 16 through 25 (of 25 total)

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