pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedMultiple outer join on same table
3+ messages / 3 participants
[nested] [flat]
* Multiple outer join on same table
@ 2004-02-09 18:12 Marco Lazzeri <marcomail@noze.it>
2004-02-11 21:23 ` Re: Multiple outer join on same table Markus Bertheau <twanger@bluetwanger.de>
2004-05-14 05:40 ` Re: Multiple outer join on same table Greg Stark <gsstark@mit.edu>
0 siblings, 2 replies; 3+ messages in thread
From: Marco Lazzeri @ 2004-02-09 18:12 UTC (permalink / raw)
To: pgsql-sql
Hi!
I'm searching a better (quicker) way to retrieve data as I used to do
using the following query...
==
SELECT
main.codice,
other.value AS value_one,
other.value AS value_two
FROM main LEFT OUTER JOIN other ON main.id =
other.id_main
LEFT OUTER JOIN other AS other2 ON main.id =
other2.id_main
WHERE
other.type = 'type_one'
AND other2.type = 'type_two';
==
Thanks,
Marco
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Multiple outer join on same table
2004-02-09 18:12 Multiple outer join on same table Marco Lazzeri <marcomail@noze.it>
@ 2004-02-11 21:23 ` Markus Bertheau <twanger@bluetwanger.de>
1 sibling, 0 replies; 3+ messages in thread
From: Markus Bertheau @ 2004-02-11 21:23 UTC (permalink / raw)
To: Marco Lazzeri <marcomail@noze.it>; +Cc: pgsql-sql
On Пнд, 2004-02-09 at 19:12 +0100, Marco Lazzeri wrote:
> Hi!
> I'm searching a better (quicker) way to retrieve data as I used to do
> using the following query...
>
> ==
> SELECT
> main.codice,
> other.value AS value_one,
> other.value AS value_two
> FROM main LEFT OUTER JOIN other ON main.id =
> other.id_main
> LEFT OUTER JOIN other AS other2 ON main.id =
> other2.id_main
> WHERE
> other.type = 'type_one'
> AND other2.type = 'type_two';
> ==
You can alias a table to join it to itself. I'm not sure whether that
answers your question.
SELECT t1.field FROM table AS t1 JOIN table as t2 on (cond)
--
Markus Bertheau <twanger@bluetwanger.de>
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Multiple outer join on same table
2004-02-09 18:12 Multiple outer join on same table Marco Lazzeri <marcomail@noze.it>
@ 2004-05-14 05:40 ` Greg Stark <gsstark@mit.edu>
1 sibling, 0 replies; 3+ messages in thread
From: Greg Stark @ 2004-05-14 05:40 UTC (permalink / raw)
To: Marco Lazzeri <marcomail@noze.it>; +Cc: pgsql-sql
Marco Lazzeri <marcomail@noze.it> writes:
> Hi!
> I'm searching a better (quicker) way to retrieve data as I used to do
> using the following query...
>
> ==
>
> SELECT main.codice,
> other.value AS value_one,
> other.value AS value_two
> FROM main
> LEFT OUTER JOIN other ON (main.id = other.id_main)
> LEFT OUTER JOIN other AS other2 ON (main.id = other2.id_main)
> WHERE other.type = 'type_one'
> AND other2.type = 'type_two'
> ;
a) you're better off sending the actual query rather than retyping it. I
assume you made a typo in the select column list and it should be
"other2.value AS value_two"? Also the parentheses are required on the ON
clause.
b) The WHERE clause will effectively make this a plain inner join, not an
outer join at all. Since any values that aren't found would have a NULL type
column and cause the row to not be selected.
I think the query you meant to write would be
SELECT codice,
other1.value AS value_one,
other2.value AS value_two
FROM main
LEFT OUTER JOIN other as other1 ON (main.id = other1.id_main AND type = 'type_one')
LEFT OUTER JOIN other as other2 ON (main.id = other2.id_main AND type = 'type_two)
Another way to write this query that might be faster or might not depending
would be:
SELECT codice,
(SELECT value FROM other WHERE id_main = id AND type = 'type_one') AS value_one,
(SELECT value FROM other WHERE id_main = id AND type = 'type_two') AS value_two
FROM codice
In theory the two queries really ought to always result in the same plan
because they're equivalent. However the Postgres optimizer as clever as it is
is incapable of seeing this.
The first form with the outer join leaves the optimizer with a lot more
flexibility though, including at least one plan that is effectively identical
to what the optimizer is forced to do for the second query. So really the
first one should be no worse than the second.
If you find the second faster (or if they're both still slow) you might
consider posting explain analyze output for both queries. It may be that you
have other issues preventing the optimizer from finding a good plan.
You have run analyze on these tables recently? And you vacuum regularly? And
for the second query you would really want an index on other.id_main too. For
the first one it would depend on the data in the two tables.
--
greg
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2004-05-14 05:40 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-02-09 18:12 Multiple outer join on same table Marco Lazzeri <marcomail@noze.it>
2004-02-11 21:23 ` Markus Bertheau <twanger@bluetwanger.de>
2004-05-14 05:40 ` Greg Stark <gsstark@mit.edu>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox