objectbridge-developers

[OJB-developers] INNER JOIN syntax

[Oleg N. <on...@uk...>]

Hi All,

I propose to use equatilies in WHERE clause for INNER JOINs.
I mean that instead of 
1) SELECT ... FROM (A INNER JOIN B ON A.FK=B.ID) INNER JOIN C ON B.FK=C.ID
we can generate
2) SELECT ... FROM A, B, C WHERE A.FK=B.ID AND B.FK=C.ID
I don't know any RDBMS that doesn't support the 2nd syntax.
Does anybody knows one?
But I know the RDBMS that doesn't support the 1st syntax: HSQL.
To be more precise, HSQL does not support nested joins such as above. 
Maybe it supports some other flavor of 1st syntax, but let's don't increase 
complexity if we can use 2nd syntax in all cases.
And the 2nd syntax seems shorter and simpler.

Regards,
 Oleg

Re: [OJB-developers] INNER JOIN syntax

[Matt G. <ma...@po...>]

Hi,

Just a couple of comments that might help ...

PostgreSQL can produce more efficient access plans when the "inner join"
syntax is used. I don't know whether that's true of other RDBMS. See
http://www.postgresql.org/idocs/index.php?explicit-joins.html.

Outer join syntax is completely different for different RDMBS. I thought
these sorts of differences were already contained in RDBMS-specific
classes. Can't they be used/extended to supply the correct inner join
syntax too?

Cheers, Matt.

p.s. One day, i hope to stop lurking on this list. I'm just waiting for
a project to use OJB with. ;-)

On Sun, 2002-05-05 at 23:58, Oleg Nitz wrote:
> Hi All,
> 
> I propose to use equatilies in WHERE clause for INNER JOINs.
> I mean that instead of 
> 1) SELECT ... FROM (A INNER JOIN B ON A.FK=B.ID) INNER JOIN C ON B.FK=C.ID
> we can generate
> 2) SELECT ... FROM A, B, C WHERE A.FK=B.ID AND B.FK=C.ID
> I don't know any RDBMS that doesn't support the 2nd syntax.
> Does anybody knows one?
> But I know the RDBMS that doesn't support the 1st syntax: HSQL.
> To be more precise, HSQL does not support nested joins such as above. 
> Maybe it supports some other flavor of 1st syntax, but let's don't increase 
> complexity if we can use 2nd syntax in all cases.
> And the 2nd syntax seems shorter and simpler.
> 
> Regards,
>  Oleg
> 
> _______________________________________________________________
> 
> Have big pipes? SourceForge.net is looking for download mirrors. We supply
> the hardware. You get the recognition. Email Us: ban...@so...
> _______________________________________________
> Objectbridge-developers mailing list
> Obj...@li...
> https://lists.sourceforge.net/lists/listinfo/objectbridge-developers
-- 
Matt Goodall, Technical Director
Pollenation Internet Ltd, http://www.pollenation.net
e: ma...@po...
t: 0113 2252500
m: 07811 278951

Re: [OJB-developers] INNER JOIN syntax

[Chris G. <CGr...@de...>]

AFAIK, MS SQL Server also performs generates optimized execution plans when
you use the "inner join" and "outer join" syntax.  It would be nice if we
could come up with a good strategy for delegating to DBMS specific
SQLGenerators for these sorts of things, and implement them as time allows.
 For the generic case, I think the "select * from A, B, C where A.id = B.id
and B.id = C.id" syntax is probably best.

Cheers,

Chris Greenlee

> Hi,
>
> Just a couple of comments that might help ...
>
> PostgreSQL can produce more efficient access plans when the "inner
> join" syntax is used. I don't know whether that's true of other RDBMS.
> See http://www.postgresql.org/idocs/index.php?explicit-joins.html.
>
> Outer join syntax is completely different for different RDMBS. I
> thought these sorts of differences were already contained in
> RDBMS-specific classes. Can't they be used/extended to supply the
> correct inner join syntax too?
>
> Cheers, Matt.
>
> p.s. One day, i hope to stop lurking on this list. I'm just waiting for
> a project to use OJB with. ;-)

Re: [OJB-developers] INNER JOIN syntax

[Oleg N. <on...@uk...>]

Thanks to everybody who posted comments!
Okay, I'll keep the "inner join" syntax wherever possible.

Oleg

On Monday 06 May 2002 03:39, you wrote:
> Hi,
>
> Just a couple of comments that might help ...
>
> PostgreSQL can produce more efficient access plans when the "inner join"
> syntax is used. I don't know whether that's true of other RDBMS. See
> http://www.postgresql.org/idocs/index.php?explicit-joins.html.
>
> Outer join syntax is completely different for different RDMBS. I thought
> these sorts of differences were already contained in RDBMS-specific
> classes. Can't they be used/extended to supply the correct inner join
> syntax too?
>
> Cheers, Matt.
>
> p.s. One day, i hope to stop lurking on this list. I'm just waiting for
> a project to use OJB with. ;-)
>
> On Sun, 2002-05-05 at 23:58, Oleg Nitz wrote:
> > Hi All,
> >
> > I propose to use equatilies in WHERE clause for INNER JOINs.
> > I mean that instead of
> > 1) SELECT ... FROM (A INNER JOIN B ON A.FK=B.ID) INNER JOIN C ON
> > B.FK=C.ID we can generate
> > 2) SELECT ... FROM A, B, C WHERE A.FK=B.ID AND B.FK=C.ID
> > I don't know any RDBMS that doesn't support the 2nd syntax.
> > Does anybody knows one?
> > But I know the RDBMS that doesn't support the 1st syntax: HSQL.
> > To be more precise, HSQL does not support nested joins such as above.
> > Maybe it supports some other flavor of 1st syntax, but let's don't
> > increase complexity if we can use 2nd syntax in all cases.
> > And the 2nd syntax seems shorter and simpler.
> >
> > Regards,
> >  Oleg
> >
> > _______________________________________________________________
> >
> > Have big pipes? SourceForge.net is looking for download mirrors. We
> > supply the hardware. You get the recognition. Email Us:
> > ban...@so... _______________________________________________
> > Objectbridge-developers mailing list
> > Obj...@li...
> > https://lists.sourceforge.net/lists/listinfo/objectbridge-developers

Re: [OJB-developers] INNER JOIN syntax

[Jakob B. <jbr...@ho...>]

hi,

mysql also optimises joins
http://www.mysql.com/doc/L/E/LEFT_JOIN_optimisation.html
so i think we should stick to the JOIN-syntax wherever possible.

jakob

----- Original Message -----
From: "Matt Goodall" <ma...@po...>
To: <obj...@li...>
Sent: Monday, May 06, 2002 2:39 AM
Subject: Re: [OJB-developers] INNER JOIN syntax


> Hi,
>
> Just a couple of comments that might help ...
>
> PostgreSQL can produce more efficient access plans when the "inner join"
> syntax is used. I don't know whether that's true of other RDBMS. See
> http://www.postgresql.org/idocs/index.php?explicit-joins.html.
>
> Outer join syntax is completely different for different RDMBS. I thought
> these sorts of differences were already contained in RDBMS-specific
> classes. Can't they be used/extended to supply the correct inner join
> syntax too?
>
> Cheers, Matt.
>
> p.s. One day, i hope to stop lurking on this list. I'm just waiting for
> a project to use OJB with. ;-)
>
> On Sun, 2002-05-05 at 23:58, Oleg Nitz wrote:
> > Hi All,
> >
> > I propose to use equatilies in WHERE clause for INNER JOINs.
> > I mean that instead of
> > 1) SELECT ... FROM (A INNER JOIN B ON A.FK=B.ID) INNER JOIN C ON
B.FK=C.ID
> > we can generate
> > 2) SELECT ... FROM A, B, C WHERE A.FK=B.ID AND B.FK=C.ID
> > I don't know any RDBMS that doesn't support the 2nd syntax.
> > Does anybody knows one?
> > But I know the RDBMS that doesn't support the 1st syntax: HSQL.
> > To be more precise, HSQL does not support nested joins such as above.
> > Maybe it supports some other flavor of 1st syntax, but let's don't
increase
> > complexity if we can use 2nd syntax in all cases.
> > And the 2nd syntax seems shorter and simpler.
> >
> > Regards,
> >  Oleg
> >
> > _______________________________________________________________
> >
> > Have big pipes? SourceForge.net is looking for download mirrors. We
supply
> > the hardware. You get the recognition. Email Us:
ban...@so...
> > _______________________________________________
> > Objectbridge-developers mailing list
> > Obj...@li...
> > https://lists.sourceforge.net/lists/listinfo/objectbridge-developers
> --
> Matt Goodall, Technical Director
> Pollenation Internet Ltd, http://www.pollenation.net
> e: ma...@po...
> t: 0113 2252500
> m: 07811 278951
>
>
> _______________________________________________________________
>
> Have big pipes? SourceForge.net is looking for download mirrors. We supply
> the hardware. You get the recognition. Email Us: ban...@so...
> _______________________________________________
> Objectbridge-developers mailing list
> Obj...@li...
> https://lists.sourceforge.net/lists/listinfo/objectbridge-developers
>

Re: [OJB-developers] INNER JOIN syntax

[James T. <jt...@4l...>]

Just wanted to point out that oracle 8(i) supports neither INNER JOIN
nor OUTER JOIN syntax. Just the ( foo = bar ) and ( foo = bar(+) )
forms. My apologies if that is not relevant to the current discussion of
SQL generation, I'm jumping onto the list mid-thread.

-- jt

On Mon, 2002-05-06 at 04:16, Jakob Braeuchi wrote:
> hi,
> 
> mysql also optimises joins
> http://www.mysql.com/doc/L/E/LEFT_JOIN_optimisation.html
> so i think we should stick to the JOIN-syntax wherever possible.
> 
> jakob
> 

Re: [OJB-developers] INNER JOIN syntax

[Oleg N. <on...@uk...>]

Hi James,

On Monday 06 May 2002 14:16, you wrote:
> Just wanted to point out that oracle 8(i) supports neither INNER JOIN
> nor OUTER JOIN syntax. Just the ( foo = bar ) and ( foo = bar(+) )
> forms. 
I am aware of that, as well as of Sybase syntax, thanks.

Oleg

Re: [OJB-developers] INNER JOIN syntax

[Matt G. <ma...@po...>]

What the database vendors should do is come up with a standard ... oh,
there is one. Still, it's only 10 years old now ;-).

The good news is that Oracle 9i *does* (finally!) support the SQL92 join
syntax.

Cheers, Matt

On Mon, 2002-05-06 at 12:50, Oleg Nitz wrote:
> Hi James,
> 
> On Monday 06 May 2002 14:16, you wrote:
> > Just wanted to point out that oracle 8(i) supports neither INNER JOIN
> > nor OUTER JOIN syntax. Just the ( foo = bar ) and ( foo = bar(+) )
> > forms. 
> I am aware of that, as well as of Sybase syntax, thanks.
> 
> Oleg
> 
> _______________________________________________________________
> 
> Have big pipes? SourceForge.net is looking for download mirrors. We supply
> the hardware. You get the recognition. Email Us: ban...@so...
> _______________________________________________
> Objectbridge-developers mailing list
> Obj...@li...
> https://lists.sourceforge.net/lists/listinfo/objectbridge-developers
-- 
Matt Goodall, Technical Director
Pollenation Internet Ltd, http://www.pollenation.net
e: ma...@po...
t: 0113 2252500
m: 07811 278951