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?

Answer 1

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.

Answer 2

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.

Answer 3

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