Querying for unmatched records in two tables

[OldNapkin]

I have two tables - One contains a list of all the zip codes in the US. The
other table is basically a mailing list containing addresses of people both
inside and outside of the US. I want to find invalid and non-US zip codes by
using a query that will tell me all of the records in the mailing list table
that have zip codes that DON'T match a zip code in the master zip code table.
I can do it with ColdFusion by selecting all the distinct zips and looping over
the results to check to see if they are in the zip code table, but that takes
forever. Is there a fast and easy way to do it in SQL? Any ideas? Thanks!

[paross1]

Perhaps something like this:

SELECT *
FROM mailing_list m
WHERE NOT EXISTS(SELECT 1
FROM zip_table z
WHERE z.zip_code = m.zip_code)

or maybe something like this:

SELECT m.*
FROM mailing_list m
INNER JOIN zip_table z
ON z.zip_code <> m.zip_code

Phil

[JMGibson3]

Use a LEFT JOIN or whatever your particular database engine calls it. For
example, in MS-Access you can get one in Design Mode by relating ZipCode from
your people table to ZipCode in your zip table. You then right-click on the
line that connects the two tables select Join Properties and select the radio
button that says something like 'Return all rows from people and only those
rows from zip where the join keys are equal'. Make sure to also include the
ZIPCode from the zip table as one of the Returned columns. When you run this
query, the ZIPCode from the zip table will be a NULL whenever there is no
match. You can also include a WHERE ziptable.ZipCode IS NULL to get only the
mismatches (peopletable.ZipCode). If you just want a list of the mismatches
rather than each mismatched record, add in a GROUP BY peopletable.ZipCode.