How to join two tables with different numbers of rows

Question

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.

• Blank: I just want to connect the first entry of my second table to the 2nd entry of my 1st table leaving the first with null value. I just want to explore sql more and the capabilities of joins. The tables were just for example

Answer 1

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

• Colin 't Hart: Why not logical thing to do? I've joined tables using calculations like this before. It comes up occasionally in business requirements, such as needing to do calculations on successive events.
• Daniel Björk: @Colin'tHart what if one or multiple events is removed then this kind of calculation will be messed up when joining on a key calculation. Instead you could use LAG() and LEAD() to get the previous or next rows values. You need to have a really specific need to do it this way. Im not saying its wrong just that you should think before doing it.

Answer 2

Maybe you want to use hierarchical query with connect by clause instead of doing something like +1

Here is documentation: https://docs.oracle.com/cd/B19306_01/server.102/b14200/queries003.htm

Answer 3

I think I find the right answer now:

SELECT a.*, b.* 
FROM samp a 
LEFT JOIN samp2 b 
ON b.rnum + 1 = a.rnum 
ORDER BY a.rnum;