Re: How do you do a negative join?

Jay,

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

Easy, two syntaxes:

SELECT allnames.name
WHERE allnames.name NOT IN (SELECT badnames.name
FROM badnames);

OR

SELECT allnames.name
WHERE NOT EXISTS (SELECT badnames.name
WHERE badnames.name = allnames.name);

The second is better for PostgreSQL versions 7.2 and 7.3. The first is better
for version 7.4.

--
-Josh Berkus
Aglio Database Solutions
San Francisco