merge two tables with no primary key but same amount of rows

Question

i have two tables, looking like that. "Value1" is something like a 2nd key, ID + Value1 = "Primary" key, but its not in the table setup and the Value1 Field is missing in the 2nd table, so i can't join it.

But the first and 2nd table has always the same amount of rows and the exact same order!

Table 1:

ID    Value1   Value2
10    1        100
10    2        200
20    1        250
30    1        150
30    2        125

Table 2:

ID   Value 3
10   50
10   60
20   70
30   80
30   25

As the result i want to that the 2nd table is merged with the first table:

ID   Value1   Value2   Value3
10   1        100      50
10   2        200      60
20   1        250      70
30   1        150      80
30   2        125      25

How to do this in SQL? Is it possible with a simple join/union, without creating new tables or something?

yes it is. It looks exactly like this. The amount of rows and the order of the data is always the same, so i just have to merge it somehow. — user3733265, Jun 16 '15 at 06:01
possible duplicate of [How to join two tables together with same number of rows by their order](http://stackoverflow.com/questions/793174/how-to-join-two-tables-together-with-same-number-of-rows-by-their-order) — Tim Biegeleisen, Jun 16 '15 at 06:02
"*and the exact same order*" - rows in relational database do ***NOT*** have "an order". You only get an ordered set if you select them using `order by`. — , Jun 16 '15 at 06:07
There's no *exact same order* as there's no guaranteed order in a table. — dnoeth, Jun 16 '15 at 06:07
*If it doesn't have a primary key, then it's not a table* - ***why*** on earth would anyone dream up a table **without** a primary key?? — marc_s, Jun 16 '15 at 06:35

score 1 · Answer 1 · answered Jun 16 '15 at 06:00

In general, in the absence of a column to JOIN by, you cannot simply merge the two tables together. Even though both tables have the same number of records and they appear ordered, in practice most RDBMS make no guarantee about the order in which each record would be either stored or selected.

You should rethink your database design and include a primary/foreign key in the 2 tables.

score 1 · Answer 2 · answered Jun 16 '15 at 06:19

In TSQL you can write as:

;with CTEtab1 as
(select 
 ID,
 Value1,
 Value2,
 row_number() over (order by Id asc) as rownum
 from tab1)
  ,CTEtab2 as
 (select 
 ID,
 Value3,
 row_number() over (order by Id asc) as rownum
 from tab2)
 select T1.Id,T1.Value1,T1.Value2,T2.Value3 
 from 
 CTEtab1 T1
 join CTEtab2 T2 
 on T1.rownum=T2.rownum

Demo

score 0 · Answer 3 · answered Jun 16 '15 at 06:20

You can do this with a simple join statement

SELECT table1.ID, table1.Value1, table1.Value2, table2.Value3
FROM table1
INNER JOIN table2
ON table1.ID = table2.ID;

though I'd still suggest altering the tables to make them have primary and foreign keys that connect them properly.

merge two tables with no primary key but same amount of rows

3 Answers3