SlavaSt SlavaSt - 2 months ago 8
SQL Question

SQL - Join two tables without a key

Given two tables (

count(Table1) <= count(Table2)
):

Table1:

record-1
record-2
...
record-k


Table2:

promo-1
promo-2
...
promo-j


Is it possible to join them into the following table? I.e. assign each entry in
Table1
some entry in
Table2
, but that no two entries in
Table1
would correspond to the same entry in
Table2
.

Result:

record-1 promo-i1
record-2 promo-i2
...
record-n promo-in

Answer

You could create a fake key using the rownum pseudo-column, and join according to that:

SELECT t1.col1, t2.col2
FROM   (SELECT   col1, ROWNUM AS rn
        FROM     table1
        ORDER BY col1) t1
JOIN   (SELECT   col2, ROWNUM AS rn
        FROM     table2
        ORDER BY col2) t2 ON t1.rn = t2.rn

EDIT:
A slightly "clunkier", yet more ANSI-friendly approach would be to use the ROW_NUMBER() window function:

SELECT t1.col1, t2.col2
FROM   (SELECT   col1, ROW_NUMBER() OVER (ORDER BY col1) AS rn
        FROM     table1) t1
JOIN   (SELECT   col2, ROW_NUMBER() OVER (ORDER BY col2) AS rn
        FROM     table2) t2 ON t1.rn = t2.rn
Comments