Thread: (+) oracle notation
Hi I just discovered the (+) oracle join notation, is there something like this in postgres ? -- Mathieu Arnold
On Thu, 19 Oct 2000, Mathieu Arnold wrote:
> I just discovered the (+) oracle join notation, is there something like
> this in postgres ?
That's the outer join operator for Oracle, right? I believe that outer
joins are coming in 7.2, but I don't know if that means the operator will
be (I don't think it's standard).
Brett W. McCoy
http://www.chapelperilous.net
---------------------------------------------------------------------------
Don't let people drive you crazy when you know it's in walking distance.
<bmccoy@chapelperilous.net> writes: > On Thu, 19 Oct 2000, Mathieu Arnold wrote: > > > I just discovered the (+) oracle join notation, is there something like > > this in postgres ? > > That's the outer join operator for Oracle, right? I believe that outer > joins are coming in 7.2, but I don't know if that means the operator will > be (I don't think it's standard). What does the operator do? I know what an outer join does, but not where that operator comes into the picture. Tomas
On 19 Oct 2000, Tomas Berndtsson wrote:
> > On Thu, 19 Oct 2000, Mathieu Arnold wrote:
> >
> > > I just discovered the (+) oracle join notation, is there something like
> > > this in postgres ?
> >
> > That's the outer join operator for Oracle, right? I believe that outer
> > joins are coming in 7.2, but I don't know if that means the operator will
> > be (I don't think it's standard).
>
> What does the operator do? I know what an outer join does, but not
> where that operator comes into the picture.
It's used, in Oracle, like this:
SELECT <fields> FROM <tables> WHERE table1.field (+) = table2.field;
This will return all appropriate rows in table1, even if no
corresponding value exists in table2 (it'll list an empty value for that
column in the output tuples if no value exists). Oracle doesn't seem to
use the OUTER JOIN keywords other databases use. You can also put the (+)
after the second field to get all of the rows in the second table even if
no value exists in the first table.
I'm looking forward to having outer joins in PostgreSQL, (+) operator or
not. It is the one feature, after foreign keys, that I had been missing
in PostgreSQL for a couple of years now and am quite happy they are making
into the next release.
Brett W. McCoy
http://www.chapelperilous.net
---------------------------------------------------------------------------
I'll be Grateful when they're Dead.
On Thu, 19 Oct 2000 bmccoy@chapelperilous.net wrote:
> > I just discovered the (+) oracle join notation, is there something like
> > this in postgres ?
>
> That's the outer join operator for Oracle, right? I believe that outer
> joins are coming in 7.2, but I don't know if that means the operator will
> be (I don't think it's standard).
Oops, I meant 7.1!
Brett W. McCoy
http://www.chapelperilous.net
---------------------------------------------------------------------------
Love is staying up all night with a sick child, or a healthy adult.
Example of its use: select a.item_number, b.group_code_description from items a, group_codes b where a.group_code = b.group_code (+); in this example all the records in table "items" appear in the output and all those records in "group codes" that matches the group_code in items. The "group_code_description" of all those non-matching records in "group_codes" table are displayed as NULL. ----- Original Message ----- From: "Tomas Berndtsson" <tomas@nocrew.org> To: "pgsql-general" <pgsql-general@postgresql.org> Sent: Thursday, October 19, 2000 12:47 PM Subject: Re: [GENERAL] (+) oracle notation > <bmccoy@chapelperilous.net> writes: > > > On Thu, 19 Oct 2000, Mathieu Arnold wrote: > > > > > I just discovered the (+) oracle join notation, is there something like > > > this in postgres ? > > > > That's the outer join operator for Oracle, right? I believe that outer > > joins are coming in 7.2, but I don't know if that means the operator will > > be (I don't think it's standard). > > What does the operator do? I know what an outer join does, but not > where that operator comes into the picture. > > > Tomas >
> SELECT <fields> FROM <tables> WHERE table1.field (+) = table2.field; > > This will return all appropriate rows in table1, even if no > corresponding value exists in table2 (it'll list an empty value for that > column in the output tuples if no value exists). In order to do that you should put the (+) operator at the "possible nulls" side of the equality. It should go this way: SELECT <fields> FROM <tables> WHERE table1.field = table2.field (+);
There is an artifical way to do the outer join in PostgreSQL... It involves using UNION and NOT IN... see : http://www.postgresql.org/docs/aw_pgsql_book/node305.html Hope this helps... -jag -- "She who is wanting me, Whose touch can make me cry, I can only understand By never asking her why..." -John Popper
I'm not sure about the standard, but I really like Oracle's notation for
foreign keys:
select a.item_number, b.group_code_description
from items a, group_codes b
where a.group_code = b.group_code (+);
Much better than
select a.item_number, b.group_code_description
from items a outer join group_codes b on a.group_code = b.group_code;
In fact, it's MUCH BETTER when you have to join several tables (one thing
PgSQL is very good at, by the way). In such cases, the seconde syntax
requires an unreadable lot of ()'s in the from clause.
Don't mean to throw away the standard though, but having Oracle's (+) syntax
around too would be a big help.
"Edmar Wiggers" <edmar@brasmap.com> writes:
> I'm not sure about the standard, but I really like Oracle's notation for
> foreign keys:
> select a.item_number, b.group_code_description
> from items a, group_codes b
> where a.group_code = b.group_code (+);
I beg to differ --- IMHO, Oracle's notation is brain dead. If they had
stuck the (+) markers on FROM-list items, it would have been sort of
reasonable, but as is, it's incomplete and ambiguous. Incomplete
because you can't specify an outer join against a table that's not
referenced anywhere in the WHERE clause. Ambiguous because it's not
clear what it means if you reference several columns from the same table
in WHERE, and tag some of them with (+) and others not. Does that mean
you get an outer join anyway? Is it an error condition? Maybe you
should implicitly get two FROM-list items, one outer joined and one not?
Worse, if you have more than two FROM-items it's very unclear what the
Oracle syntax means at all. There is a big difference between
(A CROSS JOIN B) LEFT JOIN C
and
A CROSS JOIN (B LEFT JOIN C)
not to mention
(A LEFT JOIN C) CROSS JOIN B
but who is to say which of these behaviors you will get from, say,
select ... from A, B, C where a1 = b1 and a2 = c2 (+)
? And if you reorder the terms in the WHERE, do you get a different
answer? It gets a lot worse if more than one table is outer-joined.
I don't have any great love for the ISO syntax either; it's certainly
mighty verbose. But at least you can tell what the heck it means.
regards, tom lane
Tom Lane wrote:
>
> "Edmar Wiggers" <edmar@brasmap.com> writes:
> > I'm not sure about the standard, but I really like Oracle's notation for
> > foreign keys:
>
> > select a.item_number, b.group_code_description
> > from items a, group_codes b
> > where a.group_code = b.group_code (+);
>
> I beg to differ --- IMHO, Oracle's notation is brain dead.
I agree. In my opinion the best syntax for outer joins was brought up
by informix. Ex:
SELECT a.f1, b.f1, c.f1
FROM a, OUTER( b, OUTER c )
WHERE a.key = b.fkkey
AND b.key = c.fkkey
Where b is outer from a and c outer from b. Precedence and
hierachical order is given by the parenthesis. Same example
FROM a, OUTER ( b, c)
b and c are outer from a. I find this syntax clear and logic without
any danger of missinterpretation
Best regards
Herbie
Sorry, maybe I confused you. The Oracle way: I failed to mention that (+) are specific to outer joins. There is no way to express a join in the from clause. Everything goes on the where clause: joins and "filter conditions". In the where clause, it is common practice to express to specify first your joins and after your filters. Example: select a.id,a.size,b.*,c.id,c.color from table_a a, table_b b, table_c c where a.b_id = b.id and a.c_id = c.id(+) and a.size < 1000 and b.weight > 10; This is a select from 3 tables, where a and b are regularly joined, but c is outer joined. That is, the query is likely to return null values on c.id and c.color. When you are joining 8 tables, that syntax becomes clearer. I believe the standard syntax for that might be: select a.id,a.size,b.*,c.id,c.color from ((table_a a join table_b b on a.b_id = b.id) outer join table_c c on a.c_id = b.id) where a.size < 1000 and b.weight > 10; To me, not so readable. But of course I can live with that.
Agreed. > Tom Lane wrote: > > > > "Edmar Wiggers" <edmar@brasmap.com> writes: > > > I'm not sure about the standard, but I really like Oracle's notation for > > > foreign keys: > > > > > select a.item_number, b.group_code_description > > > from items a, group_codes b > > > where a.group_code = b.group_code (+); > > > > I beg to differ --- IMHO, Oracle's notation is brain dead. > > I agree. In my opinion the best syntax for outer joins was brought up > by informix. Ex: > > SELECT a.f1, b.f1, c.f1 > FROM a, OUTER( b, OUTER c ) > WHERE a.key = b.fkkey > AND b.key = c.fkkey > > Where b is outer from a and c outer from b. Precedence and > hierachical order is given by the parenthesis. Same example > > FROM a, OUTER ( b, c) > > b and c are outer from a. I find this syntax clear and logic without > any danger of missinterpretation > > Best regards > Herbie > -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026
"Edmar Wiggers" <edmar@brasmap.com> writes:
> select a.id,a.size,b.*,c.id,c.color
> from table_a a, table_b b, table_c c
> where
> a.b_id = b.id and
> a.c_id = c.id(+) and
> a.size < 1000 and
> b.weight > 10;
> This is a select from 3 tables, where a and b are regularly joined, but c is
> outer joined. That is, the query is likely to return null values on c.id and
> c.color.
Yes, but outer joined *to what*? And what aspect of the syntax decides
that? The problem with this syntax is that it's not apparent when the
WHERE-clause conditions are applied. At least not to me.
The problem can be seen most easily when there are additional
restrictions on table C. Actually we don't need 3 tables, so consider
select * from table_a a, table_b b
where
a.id = b.id(+) and
b.weight > 10;
with data
a.id
1
2
b.id b.weight
1 20
2 5
Clearly we will get a row 1,1,20, and we will *not* get a row 2,2,5.
But will we get a row 2,NULL,NULL, or not? If the outer join occurs
after we remove b rows with weight<=10, then there will be no row
matching a.id=2, so the outer join will produce a row 2,NULL,NULL.
If it's done in the other order, the outer join will produce a row
2,2,5, which will then be discarded due to the condition weight>10,
so no row out. The ISO syntax allows both these behaviors to be
expressed unambiguously:
... FROM a LEFT JOIN b ON (a.id = b.id AND b.weight > 10)
... FROM a LEFT JOIN b ON (a.id = b.id) WHERE b.weight > 10
whereas the Oracle syntax is very ambiguous --- please don't tell me
that it depends on the ordering of the AND clauses in the WHERE!
(You may argue that 2,NULL,NULL violates the condition b.weight > 10,
but that's just an artifact of trying to simplify the example as much
as possible. If I write
where
a.id = b.id(+) and
(b.weight > 10 OR b IS NULL);
then it's absolutely unclear which result the Oracle syntax should
produce.)
It gets a lot worse if there are multiple tables being outer-joined,
since then it will depend on the join order whether you get certain
part-NULL rows out or not, and I see no way to define the join order
in the Oracle syntax.
> I believe the standard syntax for that might be:
> select a.id,a.size,b.*,c.id,c.color
> from ((table_a a join table_b b on a.b_id = b.id) outer join table_c c on
> a.c_id = b.id)
> where
> a.size < 1000 and
> b.weight > 10;
> To me, not so readable. But of course I can live with that.
Like I said, I don't much care for the ISO syntax either --- it's
very verbose. But it's unambiguous what will happen...
regards, tom lane
Got it. Thanks. Even though I'd never seen it like that, it is indeed
ambiguous.
In Oracle,
> where
> a.id = b.id(+) and
> (b.weight > 10 OR b IS NULL);
is equivalent to
> ... FROM a LEFT JOIN b ON (a.id = b.id) WHERE b.weight > 10
That is, we get row 2,NULL,NULL.
To produce
> ... FROM a LEFT JOIN b ON (a.id = b.id AND b.weight > 10)
you have to use "in-line views" (Oracle term), like
... FROM a, (select * from table_b where b.weight > 10) b
WHERE a.id = b.id(+);
I guess that finishes it. Let's use STANDARD syntax and that's it. I guess
postgres would need a lot of massage on query parsing to get oracle's
mysterious syntax right.
Hi,
The Sybase notation for outer joins seems to be good too:
*=
or
=*
Claudio
Edmar Wiggers wrote:
> I'm not sure about the standard, but I really like Oracle's notation for
> foreign keys:
>
> select a.item_number, b.group_code_description
> from items a, group_codes b
> where a.group_code = b.group_code (+);
>
> Much better than
>
> select a.item_number, b.group_code_description
> from items a outer join group_codes b on a.group_code = b.group_code;
>
> In fact, it's MUCH BETTER when you have to join several tables (one thing
> PgSQL is very good at, by the way). In such cases, the seconde syntax
> requires an unreadable lot of ()'s in the from clause.
>
> Don't mean to throw away the standard though, but having Oracle's (+) syntax
> around too would be a big help.
Excuse me,
I should have written :
I t seems to be ambiguous !!! too ...
Claudio.
Claudio Jose Zanardi Grillo wrote:
> Hi,
>
> The Sybase notation for outer joins seems to be good too:
>
> *=
> or
> =*
>
> Claudio
>
> Edmar Wiggers wrote:
>
> > I'm not sure about the standard, but I really like Oracle's notation for
> > foreign keys:
> >
> > select a.item_number, b.group_code_description
> > from items a, group_codes b
> > where a.group_code = b.group_code (+);
> >
> > Much better than
> >
> > select a.item_number, b.group_code_description
> > from items a outer join group_codes b on a.group_code = b.group_code;
> >
> > In fact, it's MUCH BETTER when you have to join several tables (one thing
> > PgSQL is very good at, by the way). In such cases, the seconde syntax
> > requires an unreadable lot of ()'s in the from clause.
> >
> > Don't mean to throw away the standard though, but having Oracle's (+) syntax
> > around too would be a big help.