SQL: Merge two result set without any conditions

Question

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?

Row by row? Preserving order? Tables would have to be the same length and it's a very non-DB thing to do... — AER, Oct 19 '22 at 14:35
Based on your update (which BTW invalidates my earlier answer below), you _can't_ achieve what you want, because your tables are lacking a second column which maintains the ordering you perceive. Keep in mind that SQL tables are modeled after sets; they have no internal order. — Tim Biegeleisen, Oct 19 '22 at 15:02

Tim Biegeleisen · Answer 1 · 2022-10-19T14:37:39.077

3

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;

edited Oct 19 '22 at 14:37

answered Oct 19 '22 at 14:35

Tim Biegeleisen

502,043
27
286
360

1

`ROW_NUMBER()` should also work for tsql as per tag as well. – AER Oct 19 '22 at 14:36
And use `OUTER JOIN` if the tables aren't of equal length – AER Oct 19 '22 at 14:36
1

@AER I have no idea why I thought I saw the MySQL tag. Regarding the left join, I will wait for the OP to also leave a comment about uneven tables. – Tim Biegeleisen Oct 19 '22 at 14:38
1

@AER - then you will need a `FULL OUTER JOIN` – PM 77-1 Oct 19 '22 at 14:40
@TimBiegeleisen I wanna save records orders in the source table – Hasan Fathi Oct 19 '22 at 14:41
Then use an `INSERT INTO ... SELECT`, where the select is the query in my answer above. – Tim Biegeleisen Oct 19 '22 at 14:42
@TimBiegeleisen Please look at my new data examples. – Hasan Fathi Oct 19 '22 at 14:45

score 1 · Accepted Answer · answered Oct 22 '22 at 07:29

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

SQL: Merge two result set without any conditions

2 Answers2