From: | Ulf Mehlig <umehlig(at)uni-bremen(dot)de> |
---|---|
To: | mlemos(at)acm(dot)org |
Cc: | pgsql-general(at)postgreSQL(dot)org |
Subject: | Re: [GENERAL] Negating the list of selected rows of a join |
Date: | 1999-03-14 08:07:00 |
Message-ID: | 199903140807.JAA01803@pandora3.uni-bremen.de |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Manuel Lemos <mlemos(at)acm(dot)org> wrote:
> 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. [...]
> 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?
If you list two (or more) tables in the 'from' clause of a select
(that is, if you do a 'join'), a result table is built, in which each
row of the first table is combined with each row from (all) the other
table(s). To clarify, do simply
SELECT table_a.name,table_b.name FROM table_a,table_b;
on your table. When one of the tables has no rows, all the rows from
the other(s) are combined with *nothing*; this gives nothing!
('combined' may be the wrong word; it's like a multiplication, and
people speak of a 'Cartesian product' of the tables)
The 'where' clause can restrict the rows of the result table to
something useful, e.g., you can restrict to 'table_a.name =
table_b.name'. A feature that probably will help you is the
construction of a so-called 'sub-select' in the where clause:
SELECT name FROM table_a
WHERE name NOT IN (SELECT name FROM table_b);
Hope it helps!
Ulf
--
======================================================================
Ulf Mehlig <umehlig(at)zmt(dot)uni-bremen(dot)de>
Center for Tropical Marine Ecology/ZMT, Bremen, Germany
----------------------------------------------------------------------
From | Date | Subject | |
---|---|---|---|
Next Message | Ulf Mehlig | 1999-03-14 08:17:20 | Re: [GENERAL] Negating the list of selected rows of a join |
Previous Message | Clark Evans | 1999-03-14 07:23:53 | Re: [GENERAL] PostgreSQL EndTransactionBlock and not inprogress/abort state |