Intersecting - 1

  • 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]

  • Intersecting question thanks 😉

    Hope this helps...

    Ford Fairlane
    Rock and Roll Detective

  • 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

  • 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

  • First time I see 99% OK.

    😀

  • 2 points!

  • 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:

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

  • 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.

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

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

  • 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.

  • 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!

  • 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 30 total)

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