DB2 Outer Join

Does anyone know that if DB2 supports outer join syntax other than the
ANSI standard ("LEFT OUTER JOIN tableX ON ")? like the one in MSSQL(" *=
") or the one in ORACLE (" =(+) ").

or it ONLY supports ANSI standard outer join?
cliffordhuiAsked:
Who is Participating?
 
kelfinkConnect With a Mentor Commented:
DB2 only supports the ANSI syntax.  There are subtle differences between the LEFT/RIGHT OUTER JOIN syntax, and the way either Oracle or SqlServer handle their proprietary syntaxes.  Both Sybase and SqlServer handle the ANSI syntax.  I often wish Oracle did.

I still occasionally refer to this comment Joe Celko posted on comp.databases.sybase.  It seems to cover the topic best.

http://groups.google.com/groups?q=celko+*%3D+left+outer+join&hl=en&rnum=3&selm=8j946f%24djv%241%40nnrp1.deja.com

hth,
Kevin
0
 
dilligaffuqCommented:
Examples of Joins
 

This example illustrates the results of the various joins using tables J1 and J2. These tables contain rows as shown.

   SELECT * FROM J1
 
   W   X    
   --- ------
   A       11
   B       12
   C       13
 
   SELECT * FROM J2
 
   Y   Z    
   --- ------
   A       21
   C       22
   D       23

The following left outer join will get back the missing row from J1 with nulls for the columns of J2. Every row from J1 is included.

   SELECT * FROM J1 LEFT OUTER JOIN J2 ON W=Y
 
   W   X      Y   Z    
   --- ------ --- ------
   A       11 A       21
   B       12 -        -
   C       13 C       22

The following right outer join will get back the missing row from J2 with nulls for the columns of J1. Every row from J2 is included.

   SELECT * FROM J1 RIGHT OUTER JOIN J2 ON W=Y
 
   W   X      Y   Z    
   --- ------ --- ------
   A       11 A       21
   C       13 C       22
   -        - D       23

The following full outer join will get back the missing rows from both J1 and J2 with nulls where appropriate. Every row from both J1 and J2 is included.

   SELECT * FROM J1 FULL OUTER JOIN J2 ON W=Y
 
   W   X      Y   Z    
   --- ------ --- ------
   A       11 A       21
   C       13 C       22
   -        - D       23
   B       12 -        -

This was found at:
http://www-4.ibm.com/cgi-bin/db2www/data/db2/udb/winos2unix/support/techlib.d2w/report
with "outer" as the search critera, then click DB2 SQL Reference and click the first example.

dill


0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.