PostgreSQL How to merge two tables row to row without condition-CodePudding

I have two tables The first table contains three text fields(username, email, num) the second have only one column with random birth_date DATE. I need to merge tables without condition For example first table:

 ---------- -------------- ----------- 
| username |    email     |    num    |
 ---------- -------------- ----------- 
| 'user1'  | 'user1@mail' | ' 794949' |
| 'user2'  | 'user2@mail' | ' 799999' |
 ---------- -------------- -----------

second table:

 -------------- 
|  birth_date  |
 -------------- 
| '2001-01-01' |
| '2002-02-02' |
 --------------

And I need result like

 ---------- ------------ ------------- -------------- 
| username |   email    |     num     |  birth_date  |
 ---------- ------------ ------------- -------------- 
| 'user1'  | 'us1@mail' | ' 7979797'  | '2001-01-01' |
| 'user2'  | 'us2@mail' | ' 79898998' | '2002-02-02' |
 ---------- ------------ ------------- --------------

I need to get in result table with 100 rows too Tried different JOIN but there is no condition here

CodePudding user response：

Sure there is a join condition, about the simplest there is: Join on true or cross join. Either is the basic merge tables without condition. However this does not result in what you want as it generates a result set of 10k rows. But you an then use limit:

select * 
  from table1 
  join table2 on true 
  order by random()
  limit 100;

select * 
  from table1 
  cross join table2
  order by random()
  limit 100;

There is other option, witch I think may be closer to what you want. Assign a value to each row of each table. Then join on this assigned value:

select <column list>
  from (select *, row_number() over() rn from table1) t1 
  join (select *, row_number() over() rn from table2) t2
    on (t1.rn = t2.rn);

To eliminate the assigned value you must specifically list each column desired in the result. But that is the way it should be done anyway.

See demo here. (demo user just 3 rows instead of 100)