How join two tables using SQL without a common column

Question

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.

Answer 1

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.