Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2003
    Posts
    223

    Unanswered: How to select the record that doesn't match another one?

    Hello, every one:

    I ahve two tables A and B that have AddreesID each other. How to select AddressID from TableB that doesn't match AddressID in TableA? Thanks.

    ZYT

  2. #2
    Join Date
    Feb 2004
    Location
    Poland
    Posts
    96
    select * from tableB where addreesID not in (select addressID from tableA)

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'd be rather surprised if this is what you really want, but to get a list of addressID values from tableB that don't exist in tableA, I'd use:
    Code:
    SELECT b.addressID
       FROM tableB as b
       WHERE NOT EXISTS (SELECT *
          FROM tableA AS a
          WHERE  a.addressID = b.addressID)
    This will produce exactly the same list of addressID values as the IN construct above, but with much less work for the server.

    -PatP

  4. #4
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    If relationship between table A and table B is one-to-one I'd use LEFT OUTER JOIN:

    select b.addressID from tableB b
    left outer join tableA a
    on b.addressID = a.addressID
    where a.addressID is null
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    How would the LEFT JOIN improve things? If nothing else, it will double the size of the return set without returning any more useful data, which I see as a bad thing.

    -PatP

  6. #6
    Join Date
    Apr 2004
    Location
    Kansas City, MO
    Posts
    734
    it will double the size of the return set without returning any more useful data, which I see as a bad thing.



    ?????? Are you smoking crack tonight Pat. It's not as efficient as NOT EXISTS usually, but it definitely doesn't double the size of the return set.
    MeanOldDBA
    derrickleggett@hotmail.com
    When life gives you a lemon, fire the DBA.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •