Need help formulating a non-equal join

lennyw · #1 (**permalink**) 05-01-09, 23:49

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

r937 · #2 (**permalink**) 05-02-09, 06:04

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

lennyw · #3 (**permalink**) 05-02-09, 10:37

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

Peter.Vanroose · #4 (**permalink**) 05-17-09, 15:57

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).

Vaibhav Vyas · #5 (**permalink**) 07-01-09, 10:45

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

Peter.Vanroose · #6 (**permalink**) 07-02-09, 18:05

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 · #7 (**permalink**) 07-02-09, 18:09

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)

Internet Services

Web Development

Digital Marketing

Consumer Tech