SQL: Merge two result set without any conditions-CodePudding

I have two tables like this:

Table1 with column N

N
---
1
2
3
4
5

And Table2 with column M:

M
---
5
9
1
8
1

Finally, I want to combine these two data sets with the same count of rows and also, save source order like this result:

N  M
------
1  5
2  9
3  1
4  8
5  1

Can anyone help me?

CodePudding user response：

Assuming you want to view this output we can use a ROW_NUMBER() trick here:

WITH cte1 AS (
    SELECT N, ROW_NUMBER() OVER (ORDER BY N) rn
    FROM Table1
),
cte2 AS (
    SELECT M, ROW_NUMBER() OVER (ORDER BY M DESC) rn
    FROM Table2
)

SELECT t1.N, t2.M
FROM cte1 t1
INNER JOIN cte2 t2
    ON t2.rn = t1.rn
ORDER BY t1.rn;

CodePudding user response：

According to Tim's answer. Also, this SO answers I could achieve my requirement and save source tables orders.

Like this:

WITH cte1 AS (
    SELECT n, ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) rn
    FROM @TempTable
),
cte2 AS (
    SELECT m, ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) rn
    FROM @TempTable2
)

SELECT t1.n, t2.m
FROM cte1 t1
INNER JOIN cte2 t2
    ON t2.rn = t1.rn
ORDER BY t1.rn

Point:

There is no need to worry about specifying constant in the ORDER BY expression.

Sources:

ROW_NUMBER Without ORDER BY

Tim's answer