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
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 |