Обсуждение: Complex outer joins?
Password: d5B9Av
Hi,
I've got PsotgreSQL 7.3.
My problem is joins. I've seen the syntax on joins and have sucessefully
used SQLs with joins, but I've got some other big SQL statements using many
and complexjoins.
Simplified example:select G.SELID, G.TEXT, L.ID as SELLEVELID , L.SELLEVEL, L.LEVELJOIN, L.LEVELTEXT, C.ID as
KRITERIENFELDID,C.SELFLD from G, L, C where and G.SELID = L.SELID (+) and L.SELID = C.SELID (+) and
L.SELLEVEL= C.SELLEVEL (+)
How can i write this in Postgres? As the problem is that I use one sametable for varios joins. This is a problem in
Postgres.Any ideas?When I use the same table in say 3 or 4 joins in one SQL, is the onlysolution unions?? Adn if so,
theperformance will certainly suffer?
thanks!
Carla
This e-mail and any attachment is for authorised use by the intended recipient(s) only. It may contain proprietary
material,confidential information and/or be subject to legal privilege. It should not be copied, disclosed to,
retainedor used by, any other party. If you are not an intended recipient then please promptly delete this e-mail and
anyattachment and all copies and inform the sender. Thank you.
Sorry if I was not that clear...but in fact the (+) is the join operator in
Oracle.
The statement in question is about making 3 left outer joins on 3 diferent
tables.
G, L and C are in fact 3 diferent tables.
Carla
-----Ursprüngliche Nachricht-----
Von: Peter Childs [mailto:blue.dragon@blueyonder.co.uk]
Gesendet: Monday, March 24, 2003 11:44 AM
Cc: 'pgsql-sql@postgresql.org'
Betreff: Re: [SQL] Complex outer joins?
On Mon, 24 Mar 2003, Correia, Carla wrote:
>
> Hi,
>
> I've got PsotgreSQL 7.3.
>
> My problem is joins. I've seen the syntax on joins and have sucessefully
> used SQLs with joins, but I've got some other big SQL statements using
many
> and complex
> joins.
> Simplified example:
>
> select G.SELID, G.TEXT,
> L.ID as SELLEVELID , L.SELLEVEL, L.LEVELJOIN, L.LEVELTEXT,
> C.ID as KRITERIENFELDID, C.SELFLD
> from G, L, C
> where
> and G.SELID = L.SELID (+)
> and L.SELID = C.SELID (+)
> and L.SELLEVEL = C.SELLEVEL (+)
>
> How can i write this in Postgres? As the problem is that I use one same
> table for varios joins. This is a problem in Postgres. Any ideas?
>
> When I use the same table in say 3 or 4 joins in one SQL, is the only
> solution unions?? Adn if so, the performance will certainly suffer?
> I don't quite understand you question. but I presume that G, L,
and C are in fact the same table. I'm not sure what you mean by the (+)
more clarity is really required.If however G, L, and C are the same table this query can be
rewritten as
select G.SELID, G.TEXT, L.ID as SELLEVELID , L.SELLEVEL, L.LEVELJOIN, L.LEVELTEXT, C.ID as KRITERIENFELDID,
C.SELFLD from table as G, table as L, table as C where and G.SELID = L.SELID and L.SELID = C.SELID
andL.SELLEVEL = C.SELLEVEL;
Where table is the name of the table....I hope that helps
Peter Childs
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to
majordomo@postgresql.org)
This e-mail and any attachment is for authorised use by the intended recipient(s) only. It may contain proprietary
material,confidential information and/or be subject to legal privilege. It should not be copied, disclosed to,
retainedor used by, any other party. If you are not an intended recipient then please promptly delete this e-mail and
anyattachment and all copies and inform the sender. Thank you.
On Mon, 24 Mar 2003, Correia, Carla wrote: > My problem is joins. I've seen the syntax on joins and have sucessefully > used SQLs with joins, but I've got some other big SQL statements using many > and complex > joins. > Simplified example: > > select G.SELID, G.TEXT, > L.ID as SELLEVELID , L.SELLEVEL, L.LEVELJOIN, L.LEVELTEXT, > C.ID as KRITERIENFELDID, C.SELFLD > from G, L, C > where > and G.SELID = L.SELID (+) > and L.SELID = C.SELID (+) > and L.SELLEVEL = C.SELLEVEL (+) > > How can i write this in Postgres? As the problem is that I use one same > table for varios joins. This is a problem in Postgres. Any ideas? I don't really understand the question, it looks like two levels of outer join to me, the first between g and l using selid and the second between that and c using selid and sellevel. What do you mean "one same table for various joins"?
"Correia, Carla" <Carla.Correia@logicacmg.com> writes:
> Simplified example:
> select G.SELID, G.TEXT,
> L.ID as SELLEVELID , L.SELLEVEL, L.LEVELJOIN, L.LEVELTEXT,
> C.ID as KRITERIENFELDID, C.SELFLD
> from G, L, C
> where
> and G.SELID = L.SELID (+)
> and L.SELID = C.SELID (+)
> and L.SELLEVEL = C.SELLEVEL (+)
> How can i write this in Postgres?
One of the un-fun things about Oracle's nonstandard syntax is that you
can't easily tell what the join order is supposed to be. (At least I
can't; anyone know how this will get interpreted?)
The SQL-standard way of writing this would presumably be either
from G left join L on (G.SELID = L.SELID) left join C on (L.SELID = C.SELID and L.SELLEVEL = C.SELLEVEL)
or
from G left join (L left join C on (L.SELID = C.SELID and L.SELLEVEL = C.SELLEVEL)) on (G.SELID =
L.SELID)
depending on which join you think ought to be done first. It might be
that the results are the same in this case, but I'm not convinced of
that. In general the results of outer joins definitely depend on join
order.
regards, tom lane
Tom Lane <tgl@sss.pgh.pa.us> writes: > The SQL-standard way of writing this would presumably be either > > from G left join L on (G.SELID = L.SELID) > left join C on (L.SELID = C.SELID and L.SELLEVEL = C.SELLEVEL) I would think of it as this one. > from G left join > (L left join C on (L.SELID = C.SELID and L.SELLEVEL = C.SELLEVEL)) > on (G.SELID = L.SELID) I don't see how that would be at all different. > depending on which join you think ought to be done first. It might be > that the results are the same in this case, but I'm not convinced of > that. In general the results of outer joins definitely depend on join > order. I'm pretty sure Oracle actually builds an abstract join representation where the two queries above would actually be represented the same way. Then decides the order from amongst the equivalent choices based on performance decisions. Can you show an example where the join order would affect the result set? I can't think of any. -- greg
On 26 Mar 2003, Greg Stark wrote: > Tom Lane <tgl@sss.pgh.pa.us> writes: > > > The SQL-standard way of writing this would presumably be either > > > > from G left join L on (G.SELID = L.SELID) > > left join C on (L.SELID = C.SELID and L.SELLEVEL = C.SELLEVEL) > > I would think of it as this one. > > > from G left join > > (L left join C on (L.SELID = C.SELID and L.SELLEVEL = C.SELLEVEL)) > > on (G.SELID = L.SELID) > > I don't see how that would be at all different. > > > depending on which join you think ought to be done first. It might be > > that the results are the same in this case, but I'm not convinced of > > that. In general the results of outer joins definitely depend on join > > order. > > I'm pretty sure Oracle actually builds an abstract join representation where > the two queries above would actually be represented the same way. Then decides > the order from amongst the equivalent choices based on performance decisions. > > Can you show an example where the join order would affect the result set? I > can't think of any. I can think of a few somewhat degenerate cases. I believe if you add an (or l.sellevel is null) to the second join's on clause. In the first if there's no match between g and l then sellevel is null and you'll join with all rows of c. In the second, you'll do that join first (and therefore only join all the rows with ones where the column really is null) and then join with g, and if there's no match, you'll get one row with nulls for the l and c columns.
Stephan Szabo <sszabo@megazone23.bigpanda.com> writes:
> On 26 Mar 2003, Greg Stark wrote:
>> Can you show an example where the join order would affect the result set? I
>> can't think of any.
> I can think of a few somewhat degenerate cases.
I don't think you need a degenerate case. Consider
from (G left join L on (G.SELID = L.SELID)) right join C on (L.SELID = C.SELID)
versus
from G left join (L right join C on (L.SELID = C.SELID)) on (G.SELID = L.SELID)
In the former case you will see rows out for every SELID existing in C;
in the latter case, rows out for every SELID existing in G, which may
include rows having no match in C. The set of joined rows is the same
in either case, but the set of rows added for unjoined keys differs.
The difference between this and the query we were discussing is just use
of "right" rather than "left" in the second JOIN. AIUI, in the Oracle
syntax this difference would be expressed by moving the (*) from one
side to the other of the L.SELID = C.SELID clause. Unless they have
strange restrictions on the combinations of clauses you can mark with
(*), I don't see how they can assume that join order is insignificant.
regards, tom lane
On Wed, 26 Mar 2003, Tom Lane wrote: > Stephan Szabo <sszabo@megazone23.bigpanda.com> writes: > > On 26 Mar 2003, Greg Stark wrote: > >> Can you show an example where the join order would affect the result set? I > >> can't think of any. > > > I can think of a few somewhat degenerate cases. > > I don't think you need a degenerate case. Consider > > from (G left join L on (G.SELID = L.SELID)) > right join C on (L.SELID = C.SELID) > > versus > > from G left join > (L right join C on (L.SELID = C.SELID)) > on (G.SELID = L.SELID) I'd noticed that too, but I was trying to do it without changing the type of join since I wasn't sure whether he'd meant one using only left joins or outer joins in general. > The difference between this and the query we were discussing is just use > of "right" rather than "left" in the second JOIN. AIUI, in the Oracle > syntax this difference would be expressed by moving the (*) from one > side to the other of the L.SELID = C.SELID clause. Unless they have > strange restrictions on the combinations of clauses you can mark with > (*), I don't see how they can assume that join order is insignificant. I'd guess that they might check that the conditions are in a specific set of limited conditions in order to allow the reordering and disallow the reordering otherwise (well, I'd hope that they do this if they ever allow reordering).
Stephan Szabo <sszabo@megazone23.bigpanda.com> writes: > On Wed, 26 Mar 2003, Tom Lane wrote: > > > Stephan Szabo <sszabo@megazone23.bigpanda.com> writes: > > > On 26 Mar 2003, Greg Stark wrote: > > >> Can you show an example where the join order would affect the result set? I > > >> can't think of any. > > > > > I can think of a few somewhat degenerate cases. > > > > I don't think you need a degenerate case. Consider > > > > from (G left join L on (G.SELID = L.SELID)) > > right join C on (L.SELID = C.SELID) > > > > versus > > > > from G left join > > (L right join C on (L.SELID = C.SELID)) > > on (G.SELID = L.SELID) > > I'd noticed that too, but I was trying to do it without changing the > type of join since I wasn't sure whether he'd meant one using only left > joins or outer joins in general. Yeah, that's not a particularly enlightening case because if you convert the right joins to left joins you see that these aren't actually similar queries at all. the first is "C left join (G left join L)" and the second is"G left join (C left join L)" They only look similar superficially when written using right joins but they're actually totally different structures. The other example using IS NULL on a column produced by the outer join is more fundamental. I'll have to ponder that one. I don't remember how Oracle behaved with cases like that because I rarely used that idiom. I think I rarely used it because I found it too confusing with Oracle's (*) syntax which I suppose begs the question. > > The difference between this and the query we were discussing is just use > > of "right" rather than "left" in the second JOIN. AIUI, in the Oracle > > syntax this difference would be expressed by moving the (*) from one > > side to the other of the L.SELID = C.SELID clause. Unless they have > > strange restrictions on the combinations of clauses you can mark with > > (*), I don't see how they can assume that join order is insignificant. There were indeed some strange restrictions on the combinations of clauses you can mark with (*). I remember running into some and being most annoyed. I don't remember precisely how it worked but my vague recollection is that it was something sort of similar to what you're describing. > I'd guess that they might check that the conditions are in a specific set > of limited conditions in order to allow the reordering and disallow the > reordering otherwise (well, I'd hope that they do this if they ever > allow reordering). I don't think they do "reordering" I think they build an abstract graph of join dependencies with constraints between tables and then optimize the ordering unconstrained by the original query. It's the only way I could imagine reaching the results I saw where semantically equivalent queries written in completely different ways reliably produced the same plans. -- greg
On 26 Mar 2003, Greg Stark wrote: > Stephan Szabo <sszabo@megazone23.bigpanda.com> writes: > > > On Wed, 26 Mar 2003, Tom Lane wrote: > > > > > from (G left join L on (G.SELID = L.SELID)) > > > right join C on (L.SELID = C.SELID) > > > > > > versus > > > > > > from G left join > > > (L right join C on (L.SELID = C.SELID)) > > > on (G.SELID = L.SELID) > > > > I'd noticed that too, but I was trying to do it without changing the > > type of join since I wasn't sure whether he'd meant one using only left > > joins or outer joins in general. > > Yeah, that's not a particularly enlightening case because if you convert the > right joins to left joins you see that these aren't actually similar queries > at all. > > the first is > "C left join (G left join L)" > and the second is > "G left join (C left join L)" > > They only look similar superficially when written using right joins but > they're actually totally different structures. Right, but it would have been an issue in converting to sql form if you had a set of conditions like C.a=L.a(+) and G.a=L.a(+) except that from testing that appears to be invalid, so you can't get into the question of which plan is correct. > The other example using IS NULL on a column produced by the outer join is more > fundamental. I'll have to ponder that one. I don't remember how Oracle behaved > with cases like that because I rarely used that idiom. I think I rarely used > it because I found it too confusing with Oracle's (*) syntax which I suppose > begs the question. You probably can't easily do IS NULL with the oracle syntax, but coalesce, case or non-strict user defined functions appear to have a similar effect (and at least in the coalesce case not rejected and give one of the two expected outputs).
Greg Stark <gsstark@mit.edu> writes:
> Yeah, that's not a particularly enlightening case because if you convert the
> right joins to left joins you see that these aren't actually similar queries
> at all.
> the first is
> "C left join (G left join L)"
> and the second is
> "G left join (C left join L)"
> They only look similar superficially when written using right joins but
> they're actually totally different structures.
Yup, you're quite right. What's bothering me is that AFAICS they are
extremely similar in the Oracle notation too: put the (*) on the other
side of one of the equality clauses, and you convert one case to the
other. So how can you assert that Oracle's notation is not sensitive
to join order?
regards, tom lane
Hi... Postgres 7.3 I'm using the copy command to fill my tables. I've got this file with the data with delimiter '\t' and 'isnull' for nulls as follows: copy mytable FROM '/usr/share/postgresql/data1/skripte/Daten/mytable' using delimiters '\t' with null as 'isnull'; But I receive an error saying that 'the value is too long for type varying(1)' This because I am filling a column defined as numeric(1) with nulls. But if I insert some values into the table with 'insert', including nulls and then do copy mytable TO '/usr/share/postgresql/data1/skripte/Daten/mytable' using delimiters '\t' with null as 'isnull'; the file looks exactly the same as my original one. Am i missing something?? thanks! Carla This e-mail and any attachment is for authorised use by the intended recipient(s) only. It may contain proprietary material,confidential information and/or be subject to legal privilege. It should not be copied, disclosed to, retainedor used by, any other party. If you are not an intended recipient then please promptly delete this e-mail and anyattachment and all copies and inform the sender. Thank you.