connect to test

   Database Connection Information

 Database server        = DB2/NT  8 . 2 . 0 
 SQL authorization ID   = DB2ADMIN
 Local database alias   = TEST


create table t1 ( id1 int not null, constraint pk primary key (id1) )
DB20000I  The SQL command completed successfully.

create table t2 ( id2 int not null, constraint pk primary key (id2) )
DB20000I  The SQL command completed successfully.

insert into t1 with tmp(id) as ( values ( 1 ) union all select tmp.id+ 1  from tmp where tmp.id< 10  ) select * from tmp
DB20000I  The SQL command completed successfully.

select * from t1

ID1        
-----------
           1 
           2 
           3 
           4 
           5 
           6 
           7 
           8 
           9 
          10 

   10  record(s) selected.


insert into t2 with tmp(id) as ( values ( 5 ) union all select tmp.id+ 1  from tmp where tmp.id< 15  ) select * from tmp
DB20000I  The SQL command completed successfully.

select * from t2

ID2        
-----------
           5 
           6 
           7 
           8 
           9 
          10 
          11 
          12 
          13 
          14 
          15 

   11  record(s) selected.


select t1.*, t2.* from t1 inner join t2 on t2.id2=t1.id1

ID1         ID2        
----------- -----------
           5             5 
           6             6 
           7             7 
           8             8 
           9             9 
          10            10 

   6  record(s) selected.


select t1.*, t2.* from t1 left outer join t2 on t2.id2=t1.id1

ID1         ID2        
----------- -----------
           5             5 
           6             6 
           7             7 
           8             8 
           9             9 
          10            10 
           1            -
           3            -
           4            -
           2            -

   10  record(s) selected.


select t1.*, t2.* from t1 right outer join t2 on t2.id2=t1.id1

ID1         ID2        
----------- -----------
           5             5 
           6             6 
           7             7 
           8             8 
           9             9 
          10            10 
          -           12 
          -           15 
          -           11 
          -           13 
          -           14 

   11  record(s) selected.


select t1.*, t2.* from t1 full outer join t2 on t2.id2=t1.id1

ID1         ID2        
----------- -----------
           5             5 
           6             6 
           7             7 
           8             8 
           9             9 
          10            10 
          -           12 
          -           15 
          -           11 
          -           13 
          -           14 
           1            -
           3            -
           4            -
           2            -

   15  record(s) selected.