Re: How do you do a negative join?

From: Bruno Wolff III <bruno(at)wolff(dot)to>
To: Jay Davis <dj00302003(at)yahoo(dot)com>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: How do you do a negative join?
Date: 2004-03-26 20:26:47
Message-ID: 20040326202647.GB20194@wolff.to
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

On Sat, Mar 20, 2004 at 13:32:08 -0800,
Jay Davis <dj00302003(at)yahoo(dot)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;

In response to

Browse pgsql-novice by date

  From Date Subject
Next Message Aarni Ruuhimäki 2004-03-26 20:52:06 Re: Images in Database
Previous Message Bruno Wolff III 2004-03-26 20:18:20 Re: Extract Function