Joining two tables together strictly by order

Question

If I have two tables(t1, t2), each with one column

t1      
letters  
a         
b         
c         

t2
nums
1
2
3

Is it possible to "join" the two together in a way that produces a two-column result set that looks like this:

letters nums
a       1
b       2
c       3

Requirements for the solution:

• Must combine each table's data in a specified order, so being able to order each table's data before joining
• Doesn't use any functions, like row_number, to add an extra column to join on

Bonus points: - If two tables have different row counts, final result set is count of the max of the two tables, and the "missing" data is nulls.

Just wondering if this is possible given the constraints.

Answer 1

You want to use row_number(). However, SQL tables represent unordered sets, so you need a column that specifies the ordering.

The idea is:

select l.letter, n.number
from (select l.*, row_number() over (order by ?) as seqnum
      from letters l
     ) l join
     (select n.*, row_number() over (order by ?) as seqnum
      from numbers n
     ) n
     on l.seqnum = n.seqnum;

The ? is for the column that specifies the ordering.

If you want all rows in both tables, use full join rather than an inner join.

EDTI:

row_number() is the obvious solution, but you can do this with a correlated subquery assuming the values are unique:

select l.letter, n.number
from (select l.*, 
             (select count(*) from letters l2 where l2.letter <= l.letter) as seqnum
      from letters l
     ) l join
     (select n.*, 
             (select count(*) from numbers n2 where n2.num <= n.num) as seqnum
      from numbers n
     ) n
     on l.seqnum = n.seqnum;

I find the restriction on not using row_number() to be rather absurd, given that it is an ISO/ANSI standard function supported by almost all databases.

Answer 2

If your version of SQL supports an ASCII function (which can generate an ASCII code for each lowercase letter), then you may join on the ASCII code shifted downwards by 96:

SELECT
    t1.letters,
    t2.nums
FROM table1 t1
INNER JOIN table2 t2
    ON t2.nums = ASCII(t1.letters) - 96;

Demo