# join and repeat value from a column of another table

I have two table A and B

Table A

``````C1
----
11
232
454
65
78
87
45
87
98
99
``````

Table B

``````C2
----
c1
c2
c3
c4
``````

How can I join these table to get output like

``````C1, C2
--------
11  ,c1
232 ,c2
454 ,c3
65  ,c4
78  ,c1
87  ,c2
45  ,c3
87  ,c4
98  ,c1
99  ,c2
``````

Since the tables have no column in common, we'll have to use the `rownum` pseudocolumn which is asigned to every row in a resultset. Do not confuse with rowid which is hash-like and exists phisycally in the table. Since rowids go 1,2,3...n, they provide a way to join two unrelated tables.

``````select
t1.c1,
t2.c2
from
(select rownum rn,c1 from a) t1 left join
(select rownum rn,c2 from b) t2 on (t1.rn=t2.rn)
``````

NOTE:

I assumed A had more rows than B so I made a left join. You can turn things around should B have more rows than A.

• I don't think it will work this way,even if A has more rows than B , after joining in above example in question the value in second column will be null after 4th row . Aug 8, 2016 at 10:19

Below query seems to work,If anybody has better solution please comment.

``````select
c1,c2
from
(select mod(rownum,(select count(1) from r2))+1 rn,customer_Key as c1 from r1) t1,
(select rownum rn,customer_Key as c2 from r2) t2
where
t1.rn=t2.rn
``````
• Please explain how it works. Aug 8, 2016 at 11:26
• using the `rownum` as you suggested , I modified the rn of table A to fall in the same range to that of the table B and then used to them join. Aug 8, 2016 at 14:40

I would take the modulus of the row numbers, using a 0 base instead of the default 1. This example assumes we know A contains more rows than B. A window function is used to pull the maximum row number with one logical read rather than calling COUNT(*) separately.

``````  WITH a_ AS (SELECT c1, ROWNUM - 1 AS rn, MAX(ROWNUM) OVER(PARTITION BY 1) AS max_rn FROM a),
b_ AS (SELECT c2, ROWNUM - 1 AS rn, MAX(ROWNUM) OVER(PARTITION BY 1) AS max_rn FROM b)
SELECT a_.c1, b_.c2
FROM a_
INNER JOIN b_
ON b_.rn = MOD(a_.rn, b_.max_rn)
ORDER BY a_.rn ASC;
``````