sqlmariadbmariadb-10.6

MariaDB concatenate 2 tables with same number of rows

Say I have 2 tables with exactly SAME number of rows, but no other obvious relations:

tableA

ID items
1 banana
2 orange

tableB

itemID volume
5550 50
5551 70

Can I join these 2 tables horizontally, to form 1 table like the following?

ID items itemID volume
1 banana 5550 50
2 orange 5551 70
Solution

  • try this

    create table TableA(ID INT, Items varchar(20));
create table TableB(ItemId INT, volume varchar(20));

insert into TableA(Id, items) values (1, 'banana'), (2, 'orange');
insert into TableB(ItemId, volume) values (5550, '50'), (5551, '70');

SELECT A.ID, A.Items, B.ItemId, B.volume
FROM
(
   SELECT ID, Items, rownum()R
   FROM TableA
)A INNER join
(
  SELECT ItemId,volume,rownum()R
  FROM TableB
)B ON A.R=B.R