Intersecting - 1

Question

Post reply

Intersecting - 1

Ron McCullough

SSC Guru

Points: 63877
More actions
February 25, 2014 at 8:05 pm

#290352

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]
February 26, 2014 at 1:05 am

This was removed by the editor as SPAM

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

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

Ford Fairlane SSCertifiable Points: 7664 More actions · Answer 1

Intersecting question thanks 😉

Hope this helps...

Ford Fairlane
Rock and Roll Detective

SQLRNNR SSC Guru Points: 281334 More actions · Answer 2

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: 259105 More actions · Answer 3

Easy one, thanks!

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

Carlo Romagnano SSC-Insane Points: 22668 More actions · Answer 4

Carlo Romagnano

SSC-Insane

Points: 22668

February 26, 2014 at 1:17 am

#1692154

First time I see 99% OK.

😀

Toreador SSChampion Points: 11365 More actions · Answer 5

Toreador

SSChampion

Points: 11365

February 26, 2014 at 1:58 am

#1692168

2 points!

paul.knibbs SSCoach Points: 15320 More actions · Answer 6

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: 11365 More actions · Answer 7

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: 15320 More actions · Answer 8

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: 10730 More actions · Answer 9

paul s-306273

SSChampion

Points: 10730

February 26, 2014 at 4:01 am

#1692206

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

Ed Wagner SSC Guru Points: 287026 More actions · Answer 10

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

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

RLilj33 SSCrazy Points: 2153 More actions · Answer 11

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 More actions · Answer 12

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: 11365 More actions · Answer 13

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.