select...except...union, Simpler query?

From: GH <grasshacker(at)over-yonder(dot)net>
To: postgresql-general(at)postgresql(dot)org
Subject: select...except...union, Simpler query?
Date: 2001-02-10 21:37:19
Message-ID: 20010210153719.A51499@over-yonder.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Can anybody think of a simpler way to run this query?

Table layout:
people
pid (unique), name_last, name_middle, name_first -- with pid::serial
p_phonenumber
pid (not unique), phone_number -- with pid::int

Query:
(select p.pid, name_first, name_last, null::numeric(10,0) as phone_number
from people p
except
select p.pid, name_first, name_last, null::numeric(10,0) as phone_number
from people p, p_phonenumber pn where p.pid=pn.pid
)
union
(select p.pid, name_first, name_last, phone_number
from people p, p_phonenumber pn where p.pid=pn.pid
)
order by pid;

I need a complete set of records (i.e. all of the records in people)
that includes phone number/s for
pid's that have it/them. A pid can have multiple phone numbers.
It seems silly to select the total set, `except` what I need, and then
`union` it with what I need. There must be a simpler way to do this,
but I cannot find it.

I thank you for your assistance.

gh

If it helps, here is the `explain` output:
NOTICE: QUERY PLAN:

Unique (cost=337.77..338.51 rows=7 width=44)
-> Sort (cost=337.77..337.77 rows=74 width=44)
-> Append (cost=0.00..335.47 rows=74 width=44)
-> Seq Scan on people p (cost=0.00..330.10 rows=61
width=28)
SubPlan
-> Materialize (cost=5.37..5.37 rows=13
width=32)
-> Hash Join (cost=1.16..5.37 rows=13
width=32)
-> Seq Scan on people p
(cost=0.00..1.61 rows=61 width=28)
-> Hash (cost=1.13..1.13 rows=13
width=4)
-> Seq Scan on p_phonenumber
pn (cost=0.00..1.13 rows=13 width=4)
-> Hash Join (cost=1.16..5.37 rows=13 width=44)
-> Seq Scan on people p (cost=0.00..1.61 rows=61
width=28)
-> Hash (cost=1.13..1.13 rows=13 width=16)
-> Seq Scan on p_phonenumber pn
(cost=0.00..1.13 rows=13 width=16)

EXPLAIN

Browse pgsql-general by date

  From Date Subject
Next Message drevil 2001-02-10 21:46:45 Strange bug in PLpgsql?
Previous Message PAOLA MANZO 2001-02-10 20:46:56 Consulta