Results 1 to 7 of 7
  1. #1
    Join Date
    Jul 2006
    Posts
    3

    Unanswered: Need help formulating a non-equal join

    I'm trying to get all records in table ADRS whose zip codes are NOT present in table EXCLUDE's zip code column.

    Th following gives me the exact inverse of what I want (e.g. all records in ADRS whose zip codes DO match some zip code in EXCLUDE:

    SELECT ADRS.First, ADRS.Last, ADRS.Adr, ADRS.City, ADRS.St, ADRS.Zip
    FROM ADRS INNER JOIN EXCLUDE ON ADRS.Zip =EXCLUDE.Zip;

    BTW, ADRS.Zip is never null.

    How can I turn this query "upside down"?

    Any help greatly appreciated

    Tnx,
    Lenny Wintfeld

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT ADRS.First
         , ADRS.Last
         , ADRS.Adr
         , ADRS.City
         , ADRS.St
         , ADRS.Zip
      FROM ADRS 
    LEFT OUTER 
      JOIN EXCLUDE 
        ON EXCLUDE.Zip = ADRS.Zip
     WHERE EXCLUDE.Zip IS NULL
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jul 2006
    Posts
    3

    Thanks for your reply

    It worked out perfectly.

    I do great with realtime and multithreaded applications but for some reason non-procedural languages like sql and xslt are a blind spot. I really should buy your book

    Many thanks. --Lenny

  4. #4
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by lennyw
    I'm trying to get all records in table ADRS whose zip codes are NOT present in table EXCLUDE's zip code column.
    Alternative is to use NOT EXISTS and a subquery:
    Code:
    SELECT First
         , Last
         , Adr
         , City
         , St
         , Zip
      FROM ADRS 
     WHERE NOT EXISTS (SELECT 1
                         FROM EXCLUDE 
                        WHERE Zip = ADRS.Zip)
    This should normally be equally performant as the outer join.
    I often perfer it over the join solution because each of the two queries refers to a single table (hence does not need the table prefix for the column names).
    Last edited by Peter.Vanroose; 05-17-09 at 16:07.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  5. #5
    Join Date
    Jul 2009
    Posts
    17

    Smile

    SELECT First
    , Last
    , Adr
    , City
    , St
    , Zip
    FROM ADRS
    WHERE Zip NOT IN (SELECT EXCLUDE.zip
    FROM EXCLUDE
    )

  6. #6
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by Vaibhav Vyas
    SELECT First
    , Last
    , Adr
    , City
    , St
    , Zip
    FROM ADRS
    WHERE Zip NOT IN (SELECT EXCLUDE.zip
    FROM EXCLUDE
    )
    Beware that this will return an empty result whenever there is a row in EXCLUDE for which ZIP is NULL.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  7. #7
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Or yet an other way to write it:
    Code:
    SELECT First
         , Last
         , Adr
         , City
         , St
         , Zip
      FROM ADRS 
     WHERE Zip IN (SELECT Zip FROM adrs
                   EXCEPT
                   SELECT Zip FROM EXCLUDE)
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

Posting Permissions

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