Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Intersecting - 1 Rate Topic Display Mode Topic Options
Author
 Message
 Posted Wednesday, February 26, 2014 4:01 AM
 SSCommitted Group: General Forum Members Last Login: Friday, December 2, 2016 3:52 AM Points: 1,715, Visits: 1,029
 Wow, like being back at school in 1972...
Post #1545308
 Posted Wednesday, February 26, 2014 5:29 AM
 SSCrazy Eights Group: General Forum Members Last Login: Today @ 2:00 PM Points: 9,935, Visits: 9,334
 Nice and simple, but it's been a long while since I've seen a question on INTERSECT or EXCEPT. Thanks.
Post #1545340
 Posted Wednesday, February 26, 2014 7:41 AM
 Ten Centuries Group: General Forum Members Last Login: Thursday, October 27, 2016 4:00 PM Points: 1,068, Visits: 414
 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
 SSCrazy Group: General Forum Members Last Login: Friday, December 2, 2016 1:54 PM Points: 2,208, Visits: 1,688
 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
 SSCrazy Group: General Forum Members Last Login: Wednesday, November 30, 2016 2:06 AM Points: 2,251, Visits: 8,029
 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
 Ten Centuries Group: General Forum Members Last Login: Thursday, October 27, 2016 4:00 PM Points: 1,068, Visits: 414
Post #1545421
 Posted Wednesday, February 26, 2014 8:11 AM
 Ten Centuries Group: General Forum Members Last Login: Monday, November 7, 2016 10:54 AM Points: 1,021, Visits: 542
 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
 Hall of Fame Group: General Forum Members Last Login: Monday, November 28, 2016 1:13 AM Points: 3,368, Visits: 3,190
 [b]Equivalent to:SELECT X AS 'Intersecting'FROM AJOIN b ON b.y = a.x;It's not equivalent, INTERSECT returns DISTINCT values.`SELECT DISTINCT X AS 'Intersecting'FROM AJOIN b ON b.y = a.x;`
Post #1545426
 Posted Wednesday, February 26, 2014 8:36 AM
 Hall of Fame Group: General Forum Members Last Login: Monday, November 28, 2016 1:13 AM Points: 3,368, Visits: 3,190
 Curious: the winner is? "EXISTS" with absolute less reads.`SET STATISTICS IO ONGOSELECT 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 Bgo SELECT DISTINCT x AS 'JOIN' FROM A JOIN B ON Y=x go `Result:EXISTS1220(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.Intersecting1220(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.JOIN1220(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
 SSCrazy Group: General Forum Members Last Login: Tuesday, March 8, 2016 10:23 AM Points: 2,144, Visits: 613
 easy one thanks..
Post #1545449

 Permissions