If you need to join the result of two queries you can use UNION ALL
clause:
select 1 as nm from dual
union all
select 2 as nm from dual;
It would be additional DISTINCT
step using UNION
without ALL
. We don't need this in this case.
If you need to generate record set with subsequential records, good solution is to "misuse" CONNECT BY
clause:
select level nm from dual connect by level <= 2; -- Instead of 2 may be any number
But this structure is not efficient in large scale. Let say you have to generate 10 millions records. In this case the best solution is to use CARTESIAN JOIN on smaller subqueries:
select rownum rn
from
(select level rn from dual connect by level <= 1000) t1,
(select level rn from dual connect by level <= 1000) t2,
(select level rn from dual connect by level <= 10) t3;
Total rowcount will be multiplication the rowcounts of all subqeries: 1,000 * 1,000 * 10 = 10,000,000.