sql to combine two unrelated tables into one

Question

I have tables

table1

col1    col2    
a       b
c       d

and table2

mycol1  mycol2
e           f
g           h
i           j
k           l

I want to combine the two tables, which have no common field into one table looking like:

table 3

col1    col2    mycol1  mycol2
a           b   e   f
c           d   g   h
null    null    i   j
null    null    k   l

ie, it is like putting the two tables side by side.

I'm stuck! Please help!

Answer 1

Get a row number for each row in each table, then do a full join using those row numbers:

WITH CTE1 AS
(
    SELECT ROW_NUMBER() OVER(ORDER BY col1) AS ROWNUM, * FROM Table1
),
CTE2 AS
(
    SELECT ROW_NUMBER() OVER (ORDER BY mycol1) AS ROWNUM, * FROM Table2
)
SELECT col1, col2, mycol1, mycol2
FROM CTE1 FULL JOIN CTE2 ON CTE1.ROWNUM = CTE2.ROWNUM

This is assuming SQL Server >= 2005.

Answer 2

It's really good if you put in a description of why this problem needs to be solved. I'm guessing it is just to practice sql syntax?

Anyway, since the rows don't have anything connecting them, we have to create a connection. I chose the ordering of their values. Also since they have nothing connecting them that also begs the question on why you would want to put them next to each other in the first place.

Here is the complete solution: http://sqlfiddle.com/#!6/67e4c/1

The select code looks like this:

WITH rankedt1 AS
(
  SELECT col1
  ,col2
  ,row_number() OVER (order by col1,col2) AS rn1
  FROM table1
  )
,rankedt2 AS 
(
  SELECT mycol1
  ,mycol2
  ,row_number() OVER (order by mycol1,mycol2) AS rn2
  FROM table2
  )

SELECT
col1,col2,mycol1,mycol2
FROM rankedt1
FULL OUTER JOIN rankedt2
  ON rn1=rn2