1

I am trying to pull two tables from an Oracle SQL database, and want to join them sequentially, so they appear as if they are one list.

List one has items [1,2,3,4] List two has items [a,b,c,d] I want to output [1,2,3,4,a,b,c,d]

Any thoughts?

philippsfrt
  • 21
  • 1
  • Possible duplicate of [What is the difference between UNION and UNION ALL?](https://stackoverflow.com/questions/49925/what-is-the-difference-between-union-and-union-all) – dossy Oct 04 '18 at 13:40

3 Answers3

1

One option uses a UNION with a computed column:

SELECT val
FROM
(
    SELECT val, 1 AS position FROM table1
    UNION ALL
    SELECT val, 2 AS position FROM table2
) t
ORDER BY
    position, val;

Demo

Note that I assume that all data here is text. If not, e.g. the first table be numeric, then we would have to do a cast along the way. But, this is not the main focus of your question anyway.

Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
0

use union, i think 1,2,3 as numeric value that why converted it on varchar as for union you have to same data type 

with t1 as (
    select 1 as id from dual union all
    select 2 from dual union all
    select 3 from dual union all
    select 4 from dual
), t2 as (
    select 'a' as item from dual union all
    select 'b' from dual union all
    select 'c' from dual union all
    select 'd' from dual
)
select cast(id as varchar(20)) as id from t1
union
select * from t2

demo

output
1
2
3
4
a
b
c
d
Zaynul Abadin Tuhin
  • 31,407
  • 5
  • 33
  • 63
  • Unless the OP wants duplicate values removed, `UNION ALL` should be used. His example didn't include duplicates, but it might be helpful to note the difference in your answer. – Patrick H Oct 04 '18 at 15:52
  • @PatrickH i answered according to sample data and there no chance of duplicating – Zaynul Abadin Tuhin Oct 04 '18 at 15:54
  • There is no benefit to using `UNION` over `UNION ALL` unless you are trying to remove duplicates. It is a performance gain to use the latter as well. Even though your answer works for his sample data, your query is doing extra unneeded work that could possibly cause problems if his actual data does in fact contain duplicates. – Patrick H Oct 04 '18 at 16:03
0
SELECT id_1, value_column1 from table_1
  UNION
    SELECT id_2, value_column2 from table_2;

if the types of columns are different - make sure you cast/convert them to char() - the resulting type should be same. https://docs.oracle.com/cd/B19306_01/server.102/b14200/queries004.htm

Deian
  • 1,237
  • 15
  • 31
  • Unless the OP wants duplicate values removed, `UNION ALL` should be used. His example didn't include duplicates, but it might be helpful to note the difference in your answer. – Patrick H Oct 04 '18 at 15:52
  • @PatrickH valid point, and that's why I added the link to the oracle documentation. – Deian Oct 04 '18 at 18:16