Joining two tables without any key

0
votes

I want to merge two tables with the same rows. each row in Table1 with corresponding row in table2.

Example:

Table1:

    | col1 |  col2  |
       ----------
    |  a   |   b   |
    |  c   |   d   |
           .
           .
           .
          etc

and Table2:

    | col3 |  col4  |
       ----------
    |  2   |   5    |
    |  8   |   10   |
           .
           .
           .
          etc

The result should be:

| col1 |  col2  | col3 | col4
------------------------------
|  a   |   b    |  2   |   5
|  c   |   d    |  8   |   10

How can I do this?

3
mysqlsqldatabasejoinmerge
How is "corresponding row" defined in this situation? - Hart CO
The tables have the same rows, first row of Table1 should merge with the first row of Table2. - BlueGirl
But how is the "first row" of each determined? Just arbitrarily? - Hart CO
Yes. There is no way of telling which rows are corresponding. SQL will not necessarily return rows in any specific order, so you cannot depend on the rows being returned in the order they were inserted. To define corresponding, you will need some sort of identity or ORDER BY column. - Beartums
This is Bad design. If you want to join two tables then you need some relation between them. Joining two tables without any relation doesn't make any sense. - Pரதீப்

3 Answers

1
votes

You need a join key for this. If you don't have one, you can create one using variables:

select t1.col1, t1.col2, t2.col3, t2.col4
from (select t1.*, (@rn1 := @rn1 + 1) as rn
      from table1 t1 cross join
           (select @rn1 := 0) vars
     ) t1 join
     (select t1.*, (@rn2 := @rn2 + 1) as rn
      from table2 t2 cross join
           (select @rn2 := 0) vars
     ) t2
     on t1.rn = t2.rn;

A caveat: SQL does not guarantee the ordering of rows without an order by clause, so the rows could be paired in any order. If you have a desired ordering, then use order by in each of the subqueries.

1
votes

Had the same issue today. I'm far away from being a mySQL Pro, but maybe it helps you in the future nonetheless.

You can create new tables using the AUTO_INCREMENT statement which automatically adds a new counting variable serving as PRIMARY KEY.

so in your case sth like this should work:

CREATE TABLE new1
(
id bigint NOT NULL AUTO_INCREMENT,
col1 bigint,
col2 bigint,
PRIMARY KEY (id)
);

CREATE TABLE new2
(
id bigint NOT NULL AUTO_INCREMENT,
col3 bigint,
col4 bigint,
PRIMARY KEY (id)
);

INSERT INTO new1 (col1, col2)
SELECT * FROM t1; 

INSERT INTO new2 (col3, col4)
SELECT * FROM t2; 

SELECT id, col1, col2, col3, col4 
FROM new1
JOIN  new2 on (new1.id = new2.id);

In your case you'd probably have to define col1/col2 as VARCHAR.

There are certainly any reasons not to proceed like this, but at least it worked quite well for me and is easy to understand.

0
votes

There's no way to reliably join 2 tables without a join key or at least an ordering key.