Re: How do you do a negative join?

"Jay Davis" <dj00302003@yahoo.com> wrote in message
news:1d17eeb7.0403201332.58f95e91@posting.google.com...
> There must be a standard SQL method to query multiple
> tables in the following way.  Lets say we have two
> tables, 'allnames' and 'badnames'. We want to get the
> following result:
>
> "select name from allnames where name-is-not-in-badnames;"
>
> Clearly I'm an SQL novice but I haven't found any examples
> of such a query in my beginning SQL books.
>
> Thanks.

Two ways of doing this:

1. Using the EXCEPT syntax provided in PostgreSQL

SELECT name FROM allnames
EXCEPT
SELECT name FROM badnames;

2. Using a LEFT JOIN and a WHERE NULL constraint (work with MySQL as well)
SELECT
    name
FROM allnames a
LEFT JOIN badnames b ON(a.name = b.name)
WHERE b.name IS NULL;

Hope that helps

--
Tom Hebbron
www.hebbron.com