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

Re: How do you do a negative join?

From: Josh Berkus <josh(at)agliodbs(dot)com>
To: dj00302003(at)yahoo(dot)com (Jay Davis), pgsql-novice(at)postgresql(dot)org
Subject: Re: How do you do a negative join?
Date: 2004-03-26 22:05:30
Message-ID: 200403261405.30653.josh@agliodbs.com (view raw or flat)
Thread:
Lists: pgsql-novice
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


In response to

pgsql-novice by date

Next:From: M. BastinDate: 2004-03-27 12:52:05
Subject: Re: Images in Database
Previous:From: Aarni RuuhimäkiDate: 2004-03-26 20:52:06
Subject: Re: Images in Database

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