SQL92 Join and DB2

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

dunleav1 - 01 Feb 2007 15:57 GMT

I have an application that uses the old join syntax instead of the
SQL92 standards join syntax.
I need to justify changing the code to the new standard.
Is there any performance issue related to using the old syntax that
are documented?
Are there any other issues that I can use to justify a code upgrade?

Reply to this Message

dunleav1 - 01 Feb 2007 16:05 GMT

Version 8.2 and 9.1

Reply to this Message

Serge Rielau - 01 Feb 2007 16:09 GMT

> I have an application that uses the old join syntax instead of the
> SQL92 standards join syntax.
> I need to justify changing the code to the new standard.
> Is there any performance issue related to using the old syntax that
> are documented?
> Are there any other issues that I can use to justify a code upgrade?

Do you mean implicit join syntax?
FROM T, S WHERE T.pk = S.FK
instead of
FROM T JOIN S ON T.pk = S.FK

There is no reason to change your code. DB2 does not differentiate
between the syntax and implicit joins are correct SQL standard syntax.
What you may want to avoid is mixing implicit join syntax with outer
join syntax for readability reasons.
When doing OUTER JOIN the ON clause gets evaluated before the WHERE
clause so throwing implicit join predicates in there may be confusing..

To sum it up: I don't see the justification.
Cheers
Serge

Signature

Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Reply to this Message

dunleav1 - 01 Feb 2007 16:46 GMT

> > I have an application that uses the old join syntax instead of the
> > SQL92 standards join syntax.
[quoted text clipped - 22 lines]
> DB2 Solutions Development
> IBM Toronto Lab

Yes - implicit join syntax
So the SQL92 Outer join syntax isn't faster or anything.

My issue is my application supports IBM DB2, Oracle, Sql Server, and
Mysql. Eveything is written in Java and the db access is through jdbc.
So having different standards accross vendor database and accross my
different product offerings could be confusing for developers.
Thanks,
JD

Reply to this Message

Serge Rielau - 01 Feb 2007 17:35 GMT

>>> I have an application that uses the old join syntax instead of the
>>> SQL92 standards join syntax.
[quoted text clipped - 24 lines]
> Yes - implicit join syntax
> So the SQL92 Outer join syntax isn't faster or anything.

Now you are talking bout OUTER join syntax.
Note that e.g. DB2 doesn't even have a non standard OUTER join syntax.
Let me try to distill this down:
Do not use (+) in Oracle or *= in SQL Server.
Oracle describes in their SQL Reference some of the funny semantics of
(+) ...

If you support more than one product then you hurt yourself with non
portable SQL.

from a DB2 perspective explicit INNER JOIN vs. implicit inner join makes
no diff whatsoevere. But I can't speak for Oracle or SQL server.

Cheers
Serge

Signature

Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Reply to this Message

dunleav1 - 01 Feb 2007 18:18 GMT

> >>> I have an application that uses the old join syntax instead of the
> >>> SQL92 standards join syntax.
[quoted text clipped - 44 lines]
> DB2 Solutions Development
> IBM Toronto Lab

Serge,
I believe we are in agreement but let me give you an example about
what I referring to:
sql89: select tab1.col1 from tab1,tab2 where tab1.col1=tab2.col and
tab1.col1 >1;
sql92: select tab1.col1 from tab1 inner join tab2 on
tab1.col1=tab2.col2 where tab1.col1 >1;
Is there a performance impact using one syntax over the other?

I agree it is a good idea to not use proprietary sql extensions such
as (tab1(+) for Oracle or Mssql *=).

Reply to this Message

Serge Rielau - 01 Feb 2007 18:29 GMT

> sql89: select tab1.col1 from tab1,tab2 where tab1.col1=tab2.col and
> tab1.col1 >1;
> sql92: select tab1.col1 from tab1 inner join tab2 on
> tab1.col1=tab2.col2 where tab1.col1 >1;
> Is there a performance impact using one syntax over the other?

In DB2: none whatsoever.

Signature

Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Reply to this Message

dunleav1 - 01 Feb 2007 18:58 GMT

> > sql89: select tab1.col1 from tab1,tab2 where tab1.col1=tab2.col and
> > tab1.col1 >1;
[quoted text clipped - 8 lines]
> DB2 Solutions Development
> IBM Toronto Lab

Thanks.

Reply to this Message