INTERSECT 2

Question

INTERSECT 2

Ron McCullough

SSC Guru

Points: 63877
More actions
June 18, 2012 at 8:55 pm

#257710

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[/url]
Before posting a performance problem please read[/url]
June 19, 2012 at 3:17 am

This was removed by the editor as SPAM

Viewing 15 posts - 1 through 15 (of 29 total)

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

vk-kirov SSCertifiable Points: 7686 More actions · Answer 1

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.

Vera-428803 SSCrazy Points: 2439 More actions · Answer 2

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

Koen Verbeeck SSC Guru Points: 259085 More actions · Answer 3

Nice question, thanks. And thanks to vk-kirov for the remark.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Koen Verbeeck SSC Guru Points: 259085 More actions · Answer 4

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.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Richard Warr SSCertifiable Points: 6963 More actions · Answer 5

All looked good to me - thanks for the question!

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

marlon.seton SSCrazy Points: 2623 More actions · Answer 6

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.

Andre Guerreiro SSCertifiable Points: 7319 More actions · Answer 7

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/MCSE/MCSA

Mighty SSCrazy Eights Points: 9063 More actions · Answer 8

I might overlook something, but aren't option 2 and 3 the same?

Koen Verbeeck SSC Guru Points: 259085 More actions · Answer 9

Mighty (6/19/2012)
I might overlook something, but aren't option 2 and 3 the same?

Nope, they are the same. The idea was probably that one of the two didn't have the DISTINCT clause.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Thomas Abraham SSChampion Points: 10761 More actions · Answer 10

Appreciate the question. Had to think for a minute, as I almost never use OUTER joins.

A totally unrelated aside: Back from vacation. Missed the QotD. Didn't miss work.

Spent 17 days in Greece. PLEASE consider a vacation there this year. The place is almost devoid of tourists, and for no good reason. In spite of their troubles, the Greeks were as gracious, warm, and hospitable as ever.

End of totally unrelated comment.

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

Mike Is Here Hall of Fame Points: 3348 More actions · Answer 11

it really helped that #2 and #3 are exactly the same, then i knew about the distinct so by process of elimination.... 1 pt.

Mike Is Here Hall of Fame Points: 3348 More actions · Answer 12

Mike Is Here

Hall of Fame

Points: 3348

June 19, 2012 at 6:09 am

#1502763

no riots?

Mike Is Here Hall of Fame Points: 3348 More actions · Answer 13

Thomas Abraham (6/19/2012)
Appreciate the question. Had to think for a minute, as I almost never use OUTER joins.
A totally unrelated aside: Back from vacation. Missed the QotD. Didn't miss work.
Spent 17 days in Greece. PLEASE consider a vacation there this year. The place is almost devoid of tourists, and for no good reason. In spite of their troubles, the Greeks were as gracious, warm, and hospitable as ever.
End of totally unrelated comment.

no riots?