Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««1234»»»

Intersecting - 1 Expand / Collapse
Author
Message
Posted Wednesday, February 26, 2014 4:01 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, October 24, 2014 5:46 AM
Points: 1,416, Visits: 804
Wow, like being back at school in 1972...
Post #1545308
Posted Wednesday, February 26, 2014 5:29 AM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 9:14 AM
Points: 4,452, Visits: 3,904
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
Post #1545340
Posted Wednesday, February 26, 2014 7:41 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Yesterday @ 12:34 PM
Points: 693, Visits: 271
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.
Post #1545396
Posted Wednesday, February 26, 2014 8:01 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 1:23 PM
Points: 1,951, Visits: 1,473
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!
Post #1545415
Posted Wednesday, February 26, 2014 8:05 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 9:26 AM
Points: 1,802, Visits: 6,571
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.
Post #1545419
Posted Wednesday, February 26, 2014 8:07 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Yesterday @ 12:34 PM
Points: 693, Visits: 271
Thanks for the reply, Toreador.
Post #1545421
Posted Wednesday, February 26, 2014 8:11 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: 2 days ago @ 9:08 AM
Points: 537, Visits: 401
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?
Post #1545424
Posted Wednesday, February 26, 2014 8:14 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 12:57 AM
Points: 2,587, Visits: 2,443
[b]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;

Post #1545426
Posted Wednesday, February 26, 2014 8:36 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 12:57 AM
Points: 2,587, Visits: 2,443
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.

Post #1545438
Posted Wednesday, February 26, 2014 9:06 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, September 15, 2014 11:49 AM
Points: 1,848, Visits: 587
easy one thanks..
Post #1545449
« Prev Topic | Next Topic »

Add to briefcase ««1234»»»

Permissions Expand / Collapse