SQL92 Join and DB2

**dunleav1** · Feb 1 '07, 04:15 PM

Re: SQL92 Join and DB2

Version 8.2 and 9.1

**Serge Rielau** · Feb 1 '07, 04:15 PM

Re: SQL92 Join and DB2

dunleav1 wrote:

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
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

**dunleav1** · Feb 1 '07, 04:55 PM

Re: SQL92 Join and DB2

On Feb 1, 11:09 am, Serge Rielau <srie...@ca.ibm .comwrote:

dunleav1 wrote:

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
--
Serge Rielau
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

**Serge Rielau** · Feb 1 '07, 05:45 PM

Re: SQL92 Join and DB2

dunleav1 wrote:

On Feb 1, 11:09 am, Serge Rielau <srie...@ca.ibm .comwrote:

>dunleav1 wrote:

>>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
>--
>Serge Rielau
>DB2 Solutions Development
>IBM Toronto Lab

>
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
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

**dunleav1** · Feb 1 '07, 06:25 PM

Re: SQL92 Join and DB2

On Feb 1, 12:35 pm, Serge Rielau <srie...@ca.ibm .comwrote:

dunleav1 wrote:

On Feb 1, 11:09 am, Serge Rielau <srie...@ca.ibm .comwrote:

dunleav1 wrote:
>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
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

>

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
--
Serge Rielau
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 *=).

**Serge Rielau** · Feb 1 '07, 06:35 PM

Re: SQL92 Join and DB2

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.

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

**dunleav1** · Feb 1 '07, 07:15 PM

Re: SQL92 Join and DB2

On Feb 1, 1:29 pm, Serge Rielau <srie...@ca.ibm .comwrote:

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.
>
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Thanks.

SQL92 Join and DB2

SQL92 Join and DB2

Comment

Comment

Comment

Comment

Comment

Comment

Comment