Create Query to join 2 tables 1 on 1 with nothing in common

Question

I have a table TAB_1 which has 230 rows.

CREATE TABLE TAB_1 (audit_id bigint NOT NULL PRIMARY KEY)

I have another table TAB_2 which also has 230 rows.

CREATE TABLE TAB_2 (employee_id bigint NOT NULL PRIMARY KEY, first_name varchar(50) NOT NULL, last_name varchar(50) NOT NULL)

Both these tables have nothing in common.

I want to write a query which will give me all the columns in these 2 tables 1 on 1 (i.e. 1st row of TAB_1 with 1st row of TAB_2, 2nd row of TAB_1 with 2nd row of TAB_2 and so on). That query will also have 230 rows.

How can I do that?

I tried these queries but they are given result as every row in TAB_1 times every row in TAB_2:

select a.audit_id, b.employee_id, b.first_name, b.last_name
from TAB_1 a inner join TAB_2 b on 1 = 1

select a.audit_id, b.employee_id, b.first_name, b.last_name
from TAB_1 a cross join TAB_2 b 

Answer 1

You need a key to join on. You can get this using row_number():

select t1.*, t2.*
from (select t1.*, row_number() over (order by audit_id) as seqnum
      from tab_1 t1
     ) t1 full outer join
     (select t2.*, row_number() over (order by employee_id) as seqnum
      from tab_2 t2
     ) t2
     on t1.seqnum = t2.seqnum;

This assumes that the ordering is based on the first column. The full outer join will return all rows, regardless of whether they have the same number of rows or not.