From: | "Tom Hebbron" <news_user(at)hebbron(dot)com> |
---|---|
To: | pgsql-novice(at)postgresql(dot)org |
Subject: | Re: How do you do a negative join? |
Date: | 2004-03-22 18:06:16 |
Message-ID: | c3n9v5$1imc$1@news.hub.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
"Jay Davis" <dj00302003(at)yahoo(dot)com> wrote in message
news:1d17eeb7(dot)0403201332(dot)58f95e91(at)posting(dot)google(dot)com(dot)(dot)(dot)
> 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.
>
> Thanks.
Two ways of doing this:
1. Using the EXCEPT syntax provided in PostgreSQL
SELECT name FROM allnames
EXCEPT
SELECT name FROM badnames;
2. Using a LEFT JOIN and a WHERE NULL constraint (work with MySQL as well)
SELECT
name
FROM allnames a
LEFT JOIN badnames b ON(a.name = b.name)
WHERE b.name IS NULL;
Hope that helps
--
Tom Hebbron
www.hebbron.com
From | Date | Subject | |
---|---|---|---|
Next Message | Mokos Péter | 2004-03-23 12:05:34 | Object Oriented features in PostgreSQL |
Previous Message | Stephan Szabo | 2004-03-22 14:32:40 | Re: postmaster proc running CPU to 100% and spinning. |