Need "eloquent" way to determine all values in one table not in another....

  • 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

  • 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. Selburg
  • 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:

    SELECT a.colA

    FROM TableA a

    LEFT JOIN TableB b

    ON a.ColA = b.ColB

    WHERE b.ColB IS NULL

    In my own experience there's no performance difference between this one and the EXCEPT syntax.

  • 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. Selburg
  • Great! I didn't know about EXCEPT!

    3 barks and a tail wag.

  • I bow (and bark) to a superior sql coder. I like your idea too.

    Barkingdog

  • 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. Selburg
  • 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:

  • 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. Selburg
  • 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.

  • 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. Selburg

Viewing 11 posts - 1 through 11 (of 11 total)

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