Merging two tables with no unique keys without row number

Question

I have two tables

Table A:

Name
----
Andy
Greg

Table B:

Value
-----
1
2

I want to merge these two tables into one:

Table C:

Result
------
Andy 1
Greg 2

Note:-

without changing the order. I cannot use row numbers as I am using Apache Calcite and it doesn't support that right now.

Is this possible?

What you're asking is (strictly speaking) impossible because SQL (and most other query languages and database systems) have no concept of a "natural" row sort-order. Some database systems return rows in the order they're physically in on-disk, but that is not deterministic (e.g. due to DBMS reusing the space used by previously deleted rows) and this behavior cannot be guaranteed (e.g. doing a database GC or internal shrink/defragmentation will change the "natural" order of the rows). This is why you must always specify an explicit `ORDER BY`. — Dai, Oct 04 '19 at 10:25
That said - the example data you've posted seems to already be lexicographical (i.e. alphabetical+numeric) order. Would it work if you did `FROM tableA ORDER BY [Name]` and `FROM tableB ORDER BY [value]`? — Dai, Oct 04 '19 at 10:26

THE LIFE-TIME LEARNER · Answer 1 · 2019-10-04T10:45:25.063

WITH X AS 
(
SELECT * FROM
(
SELECT NAME AS Val1,

(SELECT  Count(*) from #TableA a1 WHERE a1.Name < a2.Name) AS myRowNumber1 FROM #TableA a2

)a1
INNER JOIN
(
SELECT Id AS Val2,

(SELECT  Count(*) from #TableB a1 WHERE a1.Id < a2.Id) AS myRowNumber2 FROM #TableB a2 
)b1

ON a1.myRowNumber1=b1.myRowNumber2
)

SELECT Val1 +' '+ Val2 AS Result FROM X

You can use Count(*) instead of Row_Number()

OutPut:-

Result
---------

Andy 1

Greg 2

score 0 · Answer 2 · answered Oct 04 '19 at 11:42

Create a new column as an identifier

SELECT *, ROW_NUMBER() OVER(PARTITION BY NAME ORDER BY NAME) AS id
INTO #NAMES
FROM TABLE1

SELECT *, ROW_NUMBER() OVER(PARTITION BY VALUE ORDER BY VALUE) AS id
INTO #VALUES_TABLE
FROM TABLE2

And then join by the row number that will be called id

SELECT *
FROM #NAMES t1
LEFT JOIN #VALUES_TABLE t2
ON t1.id = t2.id

score 0 · Answer 3 · answered Oct 07 '19 at 23:50

Calcite does not have a function exactly like Oracle's ROWNUM pseudocolumn but it does have the standard SQL window function ROW_NUMBER(). You can use it as follows:

create table a as select * from (values ('Andy'), ('Greg')) as t (name);
create table b as select * from (values (1), (2)) as t (v);

select *
from (select name, row_number() over () as id from a)
join (select v, row_number() over () as id from b)
using (id);

+----+------+---+
| ID | NAME | V |
+----+------+---+
|  1 | Andy | 1 |
|  2 | Greg | 2 |
+----+------+---+
(2 rows)

If you want deterministic order, you can change over () to, say, over (order by name desc).

Merging two tables with no unique keys without row number

3 Answers3