Tweeted twitter.com/StackDBAs/status/899474236330446848
2 This is not Oracle PL/SQL question, but Oracle SQL question.
| link
1
source | link

How to join two tables with different numbers of rows

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.