xCloudx8 xCloudx8 - 2 months ago 8
SQL Question

Merge two tables sql

This is my data set:
Dataset 1:

col2 col3 col4
1 2 3
1 5 6


Dataset 2:

name2 name3 name4
a b c
d e l


I want to merge these two table like this:

col2 col3 col4 name2 name3 name4
1 2 3 a b c
1 5 6 d e l


I've tried with:

select * from table1 join table2 on true;


but gives me:

col2 col3 col4 name2 name3 name4
1 2 3 a b c
1 2 3 d e l


Which isn't correct. How could i achieve this?

Answer

Your result should have been four records.

You need a common key to join on, but don't have one. Here is one method:

select t1.col2, t1.col3, t1.col4, t2.name2, t2.name3, t2.name4
from (select t1.*, row_number() over () as seqnum
      from table1 t1
     ) t1 join
     (select t2.*, row_number() over () as seqnum
      from table2 t2
     ) t2
     on t1.seqnum = t2.seqnum;

Note that the ordering of the rows (defining the matching) is indeterminate. If this is important, then include order by clauses in the row_number() with the appropriate ordering.