Oracle – How to Join Two Tables with Different Row Counts

oracle

I've already search the net about this but it can't solve my problem.

I have two tables one with 10 rows and other with 9 rows

table 1 

RNUM       JOB_ID
---------- ---------- 
1          AC_ACCOUNT 
2          AC_MGR 
3          AD_ASST 
4          AD_PRES 
5          AD_VP 
6          FI_ACCOUNT 
7          FI_MGR 
8          HR_REP 
9          IT_PROG 
10         MK_MAN

table 2 

RNUM       JOB_ID 
---------- ---------- 
1          AC_ACCOUNT 
2          AC_MGR 
3          AD_ASST 
4          AD_PRES 
5          AD_VP 
6          FI_ACCOUNT 
7          FI_MGR 
8          HR_REP 
9          IT_PROG

I want to join these two tables but I want the output to be like this 

RNUM       JOB_ID            RNUM       JOB_ID 
---------- ----------        ---------- ---------- 
1          AC_ACCOUNT        null       null 
2          AC_MGR            1          AC_ACCOUNT 
3          AD_ASST           2          AC_MGR 
4          AD_PRES           3          AD_ASST 
5          AD_VP             4          AD_PRES 
6          FI_ACCOUNT        5          AD_VP 
7          FI_MGR            6          FI_ACCOUNT 
8          HR_REP            7          FI_MGR 
9          IT_PROG           8          HR_REP 
10         MK_MAN            9          IT_PROG

my first code is

select a.*, b.* 
from samp a, samp2 b where a.rnum = b.rnum(+)

but the result of my code is 

RNUM       JOB_ID                RNUM       JOB_ID
---------- ----------            ---------- ---------- 
1          AC_ACCOUNT            1          AC_ACCOUNT 
2          AC_MGR                2          AC_MGR 
3          AD_ASST               3          AD_ASST 
4          AD_PRES               4          AD_PRES 
5          AD_VP                 5          AD_VP 
6          FI_ACCOUNT            6          FI_ACCOUNT 
7          FI_MGR                7          FI_MGR 
8          HR_REP                8          HR_REP 
9          IT_PROG               9          IT_PROG 
10         MK_MAN                null       null

I want to connect table 2's number 1 entry(AC_ACCOUNT) to table 1's number 2 entry(AC_MGR) as what you can see in my desired output.

I used rownum as a technique just to have a connection between my two tables.

I think I misunderstood left outer join here, please kindly enlighten me how to achieve my desired output.

Best Answer

When joining you join one key to another x=y but you seem to want to join x=y-1 instead. In your case you want to join 1 with 0 and 2 with 1. This is not a good practice. When joining you always you should join keys with eachother without chaning them.

You should think about what you really want as a result because it is not a logical join to do. But to genereate the result you want you can do like this: 

select a.*, b.* from t1 a LEFT OUTER JOIN t2 b on a.rnum-1 = b.rnum

DB Fiddle

This is a good reference image for future use: enter image description here

Related Question