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 123»»»

INTERSECT 2 Expand / Collapse
Author
Message
Posted Monday, June 18, 2012 8:55 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:31 AM
Points: 5,572, Visits: 24,796
Comments posted to this topic are about the item INTERSECT 2

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read

Before posting a performance problem please read
Post #1317704
Posted Tuesday, June 19, 2012 12:29 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Friday, May 9, 2014 12:47 AM
Points: 3,448, Visits: 4,407
Thanks for the question.

There is one thing not mentioned in the explanation, but very important to keep in mind: the INTERSECT statement and the SELECT #4 do not always return the same results. If both tables #A and #B contain NULL values, the INTERSECT statement will return a NULL value in the result set, while the SELECT #4 won't.

This behavior is documented in Books Online (http://msdn.microsoft.com/en-us/library/ms188055.aspx)

EXCEPT and INTERSECT (Transact-SQL)
Remarks


When you compare rows for determining distinct values, two NULL values are considered equal.
Post #1317741
Posted Tuesday, June 19, 2012 1:42 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, July 1, 2014 11:59 PM
Points: 897, Visits: 355
there is no GO after creation of table #B. In my opinion the select statements will fail with an error.
I didn't try it out but that's what I suspect.
Vera
Post #1317758
Posted Tuesday, June 19, 2012 2:14 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 11:53 AM
Points: 13,310, Visits: 10,176
Nice question, thanks. And thanks to vk-kirov for the remark.



How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1317767
Posted Tuesday, June 19, 2012 2:27 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 11:53 AM
Points: 13,310, Visits: 10,176
Vera-428803 (6/19/2012)
there is no GO after creation of table #B. In my opinion the select statements will fail with an error.
I didn't try it out but that's what I suspect.
Vera


I think the statements will run just fine.




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1317772
Posted Tuesday, June 19, 2012 2:41 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 10:57 AM
Points: 2,089, Visits: 1,692
All looked good to me - thanks for the question!

Any ideas on the performance differences between the INTERSECT and INNER JOIN methods?
Post #1317781
Posted Tuesday, June 19, 2012 3:17 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 6:58 AM
Points: 3,861, Visits: 5,003
Good question, thanks Ron


____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”
Post #1317803
Posted Tuesday, June 19, 2012 3:48 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 8:03 AM
Points: 825, Visits: 319
Richard Warr (6/19/2012)
All looked good to me - thanks for the question!

Any ideas on the performance differences between the INTERSECT and INNER JOIN methods?


I suspect the explain plans and performance will be identical.
Post #1317833
Posted Tuesday, June 19, 2012 4:00 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, June 26, 2014 5:34 AM
Points: 897, Visits: 1,486
Great back-to-basics question and I got it wrong because I wasn't paying enough attention. Err...

I think there is a copy and paste mistake in SELECT #2 and SELECT #3 as both are the same code. I guess you meant one LEFT JOIN with the DISTINCT and the other without it, right?

BTW, thank you for the link with the article.


Best regards,

Andre Guerreiro Neto

Database Analyst
http://www.softplan.com.br
MCITPx1/MCTSx2
Post #1317844
Posted Tuesday, June 19, 2012 4:01 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 2:33 AM
Points: 3,198, Visits: 1,236
I might overlook something, but aren't option 2 and 3 the same?
Post #1317845
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse