Negating the list of selected rows of a join - Mailing list pgsql-general

From Manuel Lemos
Subject Negating the list of selected rows of a join
Date
Msg-id 1096.742T2345T2165246@acm.org
Whole thread Raw
Responses Re: [GENERAL] Negating the list of selected rows of a join
List pgsql-general
Hello,

I want to list the rows of a table with a text field whose values do not
exist in a similar field of another table.  Basically what I want to get
is negated results of a join.

Lets say the tables table_a and table_b have the field name.

table_a     table_b
name  age   name
----- ---   -----
Peter 27    Paul
Paul  42
Mary  20

If I asked for a join like this:

SELECT table_a.name,table_a.age FROM table_a,table_b WHERE table_a.name=table_b.name

I would get:

name  age
----- ---
Paul  42

But I want the opposite. I tried a non-equi join like this:

SELECT table_a.name,table_a.age FROM table_a,table_b WHERE table_a.name<>table_b.name

and I got:

name  age
----- ---
Peter 27
Mary  20

It worked except for the case when table_b is empty.  In this case the
nothing was returned.  Is this the expected behaviour or is it a bug in
PostgreSQL?

How can I make a query that works the way I want all the time, even for the
case when table_b is empty?


Regards,
Manuel Lemos

E-mail: mlemos@acm.org
URL: http://www.e-na.net/the_author.html
PGP key: finger://mlemos@zeus.ci.ua.pt
--


pgsql-general by date:

Previous
From: "Somnath Banerjee"
Date:
Subject: Usage of pqsql database
Next
From: "Manuel Lemos"
Date:
Subject: PostgreSQL EndTransactionBlock and not inprogress/abort state