CODEWITHSUNDEEP
sqlsql-serverselectjoinmerge
Stfvns
Stfvns

Reputation: 1041

How to join table without ON

If we have two or more tables without the same column.

Table 1

Col1 | Col2
1    |   2
3    |   4

Table 2

Col3 | Col4
 5   |   6
 7   |   8

We would like to combine these two tables and save it into a new one

New Table

Col1 | Col2 | Col3 | Col4
 1   |   2  |   5  |   6
 3   |   4  |   7  |   8

I don't have any idea what must I use. If using join it's must have same table to paring but this don't have same column

NB: I'm using SQL SERVER

Upvotes: 1

Views: 97

Answers (1)

Mureinik
Mureinik

Reputation: 311893

You could use the row_number window function to create a fake column to join on:

SELECT co1l, col2, col3, col4
FROM   (SELECT col1, col2, ROW_NUMBER() OVER (ORDER BY col1, col2) AS rn
        FROM   table1) t1
JOIN   (SELECT col3, col4, ROW_NUMBER() OVER (ORDER BY col3, col4) AS rn
        FROM   table2) t2 ON t1.rn = t2.rn

Upvotes: 4

Related Questions