How join two tables using SQL without a common column

10
votes

Example:

Table 1:

Column1           Column2
-----------       -------------
A                 1
B                 2
D                 3
E                 4

Table 2:

Column3           Column4
-----------       -------------
A                 7
E                 9
Z                 5

Expected output:

Column1           Column2             Column3            Column4
-----------       -------------       -------------      -------------
A                 1                   A                  7
B                 2                   E                  9
D                 3                   Z                  5
E                 4                   NULL               NULL

I want the output as shown in the picture.

If there are two tables with the columns Column1, Coumn2 and Column3, Column4, then the expected output should be Column1, Column2, Column3, Column4, without any joins. It should have Table2's columns to the right hand side of the Table1's columns. NULL values will consume the empty rows if the number of rows in each table don't match.

3
sql-server
just add RowNumber to each table, using Row_number() function . Then just join the two tables on RowNumber column. You can use full outer join , or if you know which table is going to have more rows then you can use left or right outer join – Sagar
Anything in stackoverflow.com/questions/12972320/… help? – lordbah

3 Answers

13
votes

You can use ROW_NUMBER window function to create a calculated field that can be used to join the two tables together:

SELECT t1.Column1, t1.Column2, t2.Column3, t2.Column4
FROM (
   SELECT Column1, Column2,
          ROW_NUMBER() OVER (ORDER BY Column1) AS rn
   FROM Table1) AS t1
FULL OUTER JOIN  (
   SELECT Column3, Column4,
          ROW_NUMBER() OVER (ORDER BY Column3) AS rn
   FROM Table2) AS t2
ON t1.rn = t2.rn

A FULL OUTER JOIN is necessary in case either Table1 or Table2 has more rows.

2
votes

This would work:-

SELECT Column1, Column2, Column3, Column4 FROM 
(SELECT ROW_NUMBER() OVER(ORDER BY Column1) row, Table1.*
FROM Table1) t1
FULL OUTER JOIN (SELECT ROW_NUMBER() OVER(ORDER BY Column3) row, Table2.*
           FROM Table2) t2
ON t1.row = t2.row

SQLFIDDLE

1
votes

Alternatively, you could also do a left join instead of full outer join:

SELECT Column1
    ,Column2
    ,t.Column3
    ,t.Column4
FROM (
    SELECT ROW_NUMBER() OVER (
            ORDER BY Column1
            ) rn
        ,Table1.*
    FROM Table1
    ) t1
LEFT JOIN (
    SELECT ROW_NUMBER() OVER (
            ORDER BY Column3
            ) AS rn
        ,t2.*
    FROM Table2 t2
    ) t ON t1.rn = t.rn;

SQL Fiddle Demo

Note: Ofcourse, this is only going to work if you have more records in TableA.