Intersecting - 1

Question

Post reply

Intersecting - 1

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

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

RLilj33 SSCrazy Points: 2153 More actions · Answer 1

RLilj33

SSCrazy

Points: 2153

February 26, 2014 at 8:07 am

#1692301

Thanks for the reply, Toreador.

samp.silvercreek Ten Centuries Points: 1170 More actions · Answer 2

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.

And the performance is better using INTERSECT, even without adding an ORDER BY to your statement (to get identical record sets).

Can someone explain the 'why' of the performance difference?

Carlo Romagnano SSC-Insane Points: 22599 More actions · Answer 3

Equivalent to:
SELECT X AS 'Intersecting'
FROM A
JOIN b ON b.y = a.x;

It's not equivalent, INTERSECT returns DISTINCT values.

SELECT DISTINCT X AS 'Intersecting'

FROM A

JOIN b ON b.y = a.x;

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

Curious: the winner is?

"EXISTS" with absolute less reads.

SET STATISTICS IO ON

GO

SELECT DISTINCT x AS 'EXISTS'

FROM A

WHERE exists(SELECT * FROM B WHERE Y=x )

GO

SELECT x AS 'Intersecting'

FROM A

INTERSECT

SELECT Y

FROM B

go

SELECT DISTINCT x AS 'JOIN'

FROM A

JOIN B

ON Y=x

go

Result:

EXISTS

1

2

20

(3 row(s) affected)

Tabella 'B'. Conteggio analisi 1, letture logiche 1, letture fisiche 0, letture read-ahead 0, letture logiche LOB 0, letture fisiche LOB 0, letture read-ahead LOB 0.

Tabella 'A'. Conteggio analisi 1, letture logiche 1, letture fisiche 0, letture read-ahead 0, letture logiche LOB 0, letture fisiche LOB 0, letture read-ahead LOB 0.

Intersecting

1

2

20

(3 row(s) affected)

Tabella 'B'. Conteggio analisi 1, letture logiche 5, letture fisiche 0, letture read-ahead 0, letture logiche LOB 0, letture fisiche LOB 0, letture read-ahead LOB 0.

Tabella 'A'. Conteggio analisi 1, letture logiche 1, letture fisiche 0, letture read-ahead 0, letture logiche LOB 0, letture fisiche LOB 0, letture read-ahead LOB 0.

JOIN

1

2

20

(3 row(s) affected)

Tabella 'Worktable'. Conteggio analisi 3, letture logiche 160, letture fisiche 0, letture read-ahead 0, letture logiche LOB 0, letture fisiche LOB 0, letture read-ahead LOB 0.

Tabella 'B'. Conteggio analisi 1, letture logiche 1, letture fisiche 0, letture read-ahead 0, letture logiche LOB 0, letture fisiche LOB 0, letture read-ahead LOB 0.

Tabella 'A'. Conteggio analisi 1, letture logiche 1, letture fisiche 0, letture read-ahead 0, letture logiche LOB 0, letture fisiche LOB 0, letture read-ahead LOB 0.

PChiragS SSCarpal Tunnel Points: 4965 More actions · Answer 5

PChiragS

SSCarpal Tunnel

Points: 4965

February 26, 2014 at 9:06 am

#1692326

easy one thanks..

Ken SSCoach Points: 17191 More actions · Answer 6

I always use Exists and Not Exists instead of Intersect and Except. I have never seen Intersect used in any DB that I have worked on.

Revenant SSC-Forever Points: 42467 More actions · Answer 7

Revenant

SSC-Forever

Points: 42467

February 26, 2014 at 10:59 am

#1692370

Nice and easy - thanks, Ron!

Thomas Abraham SSChampion Points: 10761 More actions · Answer 8

LIKE the question on INTERSECT. Basic, but very useful. Thanks Ron!

[font="Verdana"]Please don't go. The drones need you. They look up to you.[/font]
Connect to me on LinkedIn

free_mascot One Orange Chip Points: 27169 More actions · Answer 9

Easy one, Thanks Ron.

---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."

Amit Raut SSCrazy Points: 2613 More actions · Answer 10

Nice to recollect INTERSECT. Basic, but I've almost forgotten to use it. Thanks Ron!

Hany Helmy SSChampion Points: 13921 More actions · Answer 11

Carlo Romagnano (2/26/2014)
First time I see 99% OK.
😀

Now it`s 95% still one of the easiest QotD ever.

Thanks & Best Regards,
Hany Helmy
SQL Server Database Consultant

Hany Helmy SSChampion Points: 13921 More actions · Answer 12

paul.knibbs (2/26/2014)
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:

I thought there was some trick; but it is that simple 🙂

Thanks & Best Regards,
Hany Helmy
SQL Server Database Consultant