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;