October 18, 2007 at 4:16 pm
I have two tables, say TableA with col A and TableB with col B. I want to find all records in TableA , col A that are not in table B, col B.
I have coded this type of thing several times with clumsy constructs such as
SELECT colA from TableA where ColA not in
(select ** from TableA join Table B
ON TableA.colA = TableB.colB)
but would like a more "eloquent" t-sql way of doing this.
TIA,
barkingdog
October 18, 2007 at 4:47 pm
It's not necessarily eloquent, but it's new :hehe:
SELECT colA from TableA
EXCEPT
SELECT colB from TableB
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgOctober 18, 2007 at 5:12 pm
EXCEPT is a newer syntax in SS2K5. The traditional method for this (and much better performing than NOT IN) is what's called a left anti-semi join like this:
SELECTa.colA
FROMTableA a
LEFT JOINTableB b
ONa.ColA = b.ColB
WHEREb.ColB IS NULL
In my own experience there's no performance difference between this one and the EXCEPT syntax.
October 18, 2007 at 5:22 pm
Aaron,
I agree. And ELEGANCE is in the eye of the beholder... :Whistling:
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgOctober 18, 2007 at 5:26 pm
Great! I didn't know about EXCEPT!
3 barks and a tail wag.
October 18, 2007 at 5:27 pm
I bow (and bark) to a superior sql coder. I like your idea too.
Barkingdog
October 18, 2007 at 5:28 pm
There a a few usefull new keywords in '05
EXCEPT, INTERSECT, and ROW_NUMBER just to name a few...
Wag that tail all you want, just don't go poo on the carpet! :hehe:
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgOctober 18, 2007 at 5:32 pm
I always try to think of "elegant" as being synonymous with "speedy" ;). Though when I first read about the new except/intersect syntax, I was definitely interested to find out how it performed against common join techniques for getting the same result. Unfortunately I couldn't find a performance benefit to either. :unsure:
October 18, 2007 at 5:38 pm
Aaron,
I agree. I haven't come across a situation where either preform better. I guess it's a lot like Common table Expressions, in that they are function identical as temp tables, but off "another" way to write code that is easier to read. I'm not saying that the LEFT JOIN ... WHERE .. IS NULL method is more difficult to read/understand than the EXCEPT version, only that to newbies, I can see it might be.....
I guess it's a matter of preference and that's about it.
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgOctober 18, 2007 at 5:56 pm
Oh totally, if I was just learning today I can guarantee you that I'd probably use EXCEPT. But I've been doing left anti-semi joins (and it rolls off the tongue so well) for several years now. Not to mention back-compatibility issues.
And you know what I always say: If it wasn't broken to begin with, there's no reason to spend resources or time trying to fix it.
October 18, 2007 at 6:02 pm
Just like "Why get a Barkingdog fixed if he ain't broke" LOL
Man, that's just wrong on so many levels....:hehe:
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgViewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply