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, July 4, 2014 9:03 AM
Points: 1,415, Visits: 796
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 @ 1:51 PM
Points: 4,339, Visits: 3,387
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:06 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 8:39 AM
Points: 2,935, Visits: 239
I guessed right.
Post #1545375
Posted Wednesday, February 26, 2014 7:41 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Today @ 8:04 AM
Points: 636, Visits: 245
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: Today @ 7:19 AM
Points: 1,878, Visits: 1,413
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: Friday, August 29, 2014 10:20 AM
Points: 1,740, Visits: 6,366
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
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Today @ 8:04 AM
Points: 636, Visits: 245
Thanks for the reply, Toreador.
Post #1545421
Posted Wednesday, February 26, 2014 8:11 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 11:12 AM
Points: 469, Visits: 340
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:53 AM
Points: 2,529, Visits: 2,402
[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:53 AM
Points: 2,529, Visits: 2,402
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
« Prev Topic | Next Topic »

Add to briefcase ««1234»»»

Permissions Expand / Collapse