Intersecting - 1

  • Ron McCullough

    SSC Guru

    Points: 63877

    Comments posted to this topic are about the item Intersecting - 1

    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

    Intersecting question thanks 😉

    Hope this helps...

    Ford Fairlane
    Rock and Roll Detective

  • SQLRNNR

    SSC Guru

    Points: 281243

    Thanks Ron

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • Koen Verbeeck

    SSC Guru

    Points: 258965

    Easy one, thanks!

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • This was removed by the editor as SPAM

  • Carlo Romagnano

    SSC-Insane

    Points: 21987

    First time I see 99% OK.

    😀

  • Toreador

    SSChampion

    Points: 11257

    2 points!

  • paul.knibbs

    SSCoach

    Points: 15270

    I had to go and run this one because I couldn't believe the answer was as simple as it first appeared. Lo and behold, it *was* that simple! :laugh:

  • Toreador

    SSChampion

    Points: 11257

    Of course the correct answer isn't an option. It fails with an error saying that column X does not exist...

  • paul.knibbs

    SSCoach

    Points: 15270

    Toreador (2/26/2014)


    Of course the correct answer isn't an option. It fails with an error saying that column X does not exist...

    Depends on the collation setting for the database, doesn't it? I believe the default install option is for a case-insensitive collation, in which case, the script as presented works fine.

  • paul s-306273

    SSChampion

    Points: 10615

    Wow, like being back at school in 1972...

  • Ed Wagner

    SSC Guru

    Points: 286983

    Nice and simple, but it's been a long while since I've seen a question on INTERSECT or EXCEPT. Thanks.

  • RLilj33

    SSCrazy

    Points: 2153

    Equivalent to:

    SELECT X AS 'Intersecting'

    FROM A

    JOIN b ON b.y = a.x;

    Except the ordering. INTERSECT returns the values in numeric order; JOIN returns the values in the order in which they were found in the left table (A).

    I didn't see anything in the BOL documentation on this. Anyone shed some light on this? Thanks.

  • Dana Medley

    SSCertifiable

    Points: 6764

    Thanks for the question Ron. Here lately intersect and except are two of my most used commands; I didn't even look for the got'cha in this one. 🙂



    Everything is awesome!

  • Toreador

    SSChampion

    Points: 11257

    RLilj33 (2/26/2014)


    Equivalent to:

    SELECT X AS 'Intersecting'

    FROM A

    JOIN b ON b.y = a.x;

    Except the ordering. INTERSECT returns the values in numeric order; JOIN returns the values in the order in which they were found in the left table (A).

    I didn't see anything in the BOL documentation on this. Anyone shed some light on this? Thanks.

    Neither version is guaranteed to return the values in any particular order. The results you are seeing are determined purely by the plan that the optimiser chooses - this could change with different volumes of data, or between different versions of SQLServer, etc.

    The only way to guarantee a particular sequence is to specify an Order By clause.

Viewing 15 posts - 1 through 15 (of 31 total)

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