Join Tables with no Join Criteria

Question

This seems so simple, but I just can't figure it out. I want to simply join 2 tables together. I don't care which values are paired with which. Using TSQL, here is an example:

declare @tbl1 table(id int)  
declare @tbl2 table(id int)  

insert @tbl1 values(1)  
insert @tbl1 values(2)  
insert @tbl2 values(3)  
insert @tbl2 values(4)  
insert @tbl2 values(5)  

select * from @tbl1, @tbl2  

This returns 6 rows, but what kind of query will generate this (just slap the tables side-by-side):
1 3
2 4
null 5

Answer 1

Use Cross Join

  Select * From tableA CrossJoin TableB

But understand you will get a row in the output for every combination of rows in TableA with every Row in TableB...

So if Table A has 8 rows, and TableB has 4 rows, you will get 32 rows of data... If you want any less than that, you have to specify some join criteria, that will filter out the extra rows from the output

Answer 2

You can give each table row numbers and then join on the row numbers:

WITH
Table1WithRowNumber as (
    select row_number() over (order by id) as RowNumber, id from Table1
),

Table2WithRowNumber as (
    select row_number() over (order by id) as RowNumber, id from Table2
)

SELECT Table1WithRowNumber.Id, Table2WithRowNumber.Id as Id2
FROM Table1WithRowNumber 
FULL OUTER JOIN Table2WithRowNumber ON Table1WithRowNumber.RowNumber = Table2WithRowNumber.RowNumber

Edit: Modiifed to use FULL OUTER JOIN, so you get all rows (with nulls).

Answer 3

The SQL1 cross join applies here also.

Select * 
From tableA, TableB

Answer 4

Well, this will work:

Select A.ID, B.ID From
  (SELECT ROW_NUMBER () OVER (ORDER BY ID) AS RowNumber, ID FROM Tbl2 ) A
full outer join
  (SELECT ROW_NUMBER () OVER (ORDER BY ID) AS RowNumber, ID FROM Tbl1 ) B 
on (A.RowNumber=B.RowNumber)