Combining columns from two unrelated tables in SQL

Question

Let's say I have two tables

Table 1

COl_1 COl_2

1     5
2     6
3     7
4     8

AND Table 2

COL3    COL4
9      13
10     14
11     15
12     16

I want the following:

COL_1   COL_2   COL3    COL4
1        5        9     13
2        6        10    14
3        7        11    15
4        8        12    16

Also, the number of rows are exactly the same everytime in each of the tables, no key relation or integrity stuff.

Any clue?

Answer 1

You can do this using row_number() to add a "join" key to the two tables:

select t1.col_1, t1.col_2, t2.col_3, t2.col_4
from (select t1.*, row_number() over (order by col_1) as seqnum
      from table1 t1
     ) t1 join
     (select t2.*, row_number() over (order by col_3) as seqnum
      from table2 t2
     ) t2
     on t1.seqnum = t2.seqnum;

If the tables have different numbers of rows, you might want an outer join.

Answer 2

You can add an identity column as an alternative to row_number() as follows

alter table Table_1 add id int identity(1,1)
alter table Table_2 add id int identity(1,1)

Then using this ID column, you can join both tables as follows (as LONG suggested FULL JOIN)

select col1, col2, COL3, COL4
from Table_1
full outer join Table_2 on Table_1.id = Table_2.id

With some additional data you can get following results