Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-novice by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group