CODEWITHSUNDEEP
sql
tempid
tempid

Reputation: 8198

Combine 2 result sets in SQL?

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.

Upvotes: 2

Views: 7807

Answers (3)

Adam Schmideg
Adam Schmideg

Reputation: 10850

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

Upvotes: 0

Mark Byers
Mark Byers

Reputation: 837946

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.

Upvotes: 1

Martin Smith
Martin Smith

Reputation: 452977

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

Upvotes: 7

Related Questions