Обсуждение: How do you do a negative join?

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.

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

On Sat, Mar 20, 2004 at 13:32:08 -0800,
  Jay Davis <dj00302003@yahoo.com> wrote:
> 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.

These don't all have the same semantics, but in common cases (where name
is a primary key) they will all give the same result. If there are NULLs
or repeated values then you need to think about which one you want.

select name from allnames where name not in (select name from badnames);
select name from allnames where not exists(
  select 1 from badnames where allnames.name = badnames.name);
select name from allnames except select name from badnames;