From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Stephan Szabo <sszabo(at)megazone23(dot)bigpanda(dot)com> |
Cc: | Nicolas Fertig <nfertig(at)swissnow(dot)ch>, pgsql-sql(at)postgresql(dot)org |
Subject: | Re: OUTER JOIN with filter |
Date: | 2003-03-02 07:09:17 |
Message-ID: | 16210.1046588957@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
Stephan Szabo <sszabo(at)megazone23(dot)bigpanda(dot)com> writes:
> On Sat, 1 Mar 2003, Nicolas Fertig wrote:
>> Can anyone help me with the problem bellow ?
>>
>> SELECT TM.some_field, TS.name
>> FROM table_main TM
>> LEFT OUTER JOIN (SELECT name FROM table_slave WHERE c1 = 'myvalue') TS
>> USING(id)
> select tm.some_field, ts.name
> from table_main tm left outer join table_slave ts
> on (tm.id=ts.id and ts.c1='myvalue');
Offhand I believe that these queries should generate identical plans.
They do not at the moment --- the second one generates a worse plan
(sorry Stephan ;-)) --- because the planner does not realize it could
push down the ts.c1='myvalue' JOIN condition into the scan of ts, even
though the join is OUTER. But AFAICS it would not change the results to
do so; ts rows failing ts.c1='myvalue' will not produce join output
anyway, but would allow outer-joined lefthand rows to be produced. (Can
anyone see a hole in that logic? It's on my to-do list to change it...)
I concur with the other comments that there's nothing wrong with the
given query ... other than such trivial details as it doesn't work ...
the sub-select doesn't produce an ID column. So I think Nicolas is
giving us an over-censored description of his real problem; but without
the omitted details, we're not likely to offer much useful help.
regards, tom lane
From | Date | Subject | |
---|---|---|---|
Next Message | Stephan Szabo | 2003-03-02 07:33:00 | Re: OUTER JOIN with filter |
Previous Message | Tom Lane | 2003-03-02 06:10:05 | Re: |