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 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
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
From | Date | Subject | |
---|---|---|---|
Next Message | M. Bastin | 2004-03-27 12:52:05 | Re: Images in Database |
Previous Message | Aarni Ruuhimäki | 2004-03-26 20:52:06 | Re: Images in Database |