Combine 2 result sets in SQL?

Question

How do I combine the resultsets to return a single result in SQL? For example -

SELECT * FROM Table1
SELECT * FROM Table2

I want to combine the two resultsets with the columns from the second resultset appended to the first.

Table 1 and Table 2 are not related to each other in any way. If Table 1 has 2 columns and Table 2 has 4 columns, I wanted 6 columns returned total in a single resultset. And if Table 1 has 4 rows and Table 2 has only 2 rows, I want NULLS in Table 2 rows for 3rd and 4th row.

Is it possible?

Edit: I do not know how many columns are present in Table1 and Table2, so cannot use UNION with nulls.

Answer 1

If your RDBMS supports ROW_NUMBER() you could do something like this.

WITH T1 AS
(
SELECT *, ROW_NUMBER() OVER ( ORDER BY T1id) AS RN1 FROM Table1
),
T2 AS
(
SELECT *, ROW_NUMBER() OVER ( ORDER BY T2id) AS RN2 FROM Table2
)


SELECT * FROM T1 FULL OUTER JOIN T2 ON RN1 =RN2

Answer 2

It's possible but it's probably quite a bad idea to do this. Why not just run two queries?

If you really want to do it, join the two result sets on a ROW_NUMBER() field.

Answer 3

You'll could match the columns and then use UNION, e.g.

SELECT a, b, c, NULL, NULL, NULL FROM Table1
UNION
SELECT NULL, NULL, NULL, d, e, f FROM Table2

Answer 4

Not a general solution, but works if you know your schema:

select a1, a2, null as b1, null as b2 from table1
union
select null as a1, null as a2, b1, b2 from table2