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

Re: How do you do a negative join?

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$ (view raw, whole thread or download thread mbox)
Lists: pgsql-novice
"Jay Davis" <dj00302003(at)yahoo(dot)com> wrote in message 
> 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
SELECT name FROM badnames;

2. Using a LEFT JOIN and a WHERE NULL constraint (work with MySQL as well)
FROM allnames a
LEFT JOIN badnames b ON( =

Hope that helps

Tom Hebbron

In response to

pgsql-novice by date

Next:From: Mokos P├ęterDate: 2004-03-23 12:05:34
Subject: Object Oriented features in PostgreSQL
Previous:From: Stephan SzaboDate: 2004-03-22 14:32:40
Subject: Re: postmaster proc running CPU to 100% and spinning.

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