1

table 1

id | name | gender  
1  | ABC  |  M  
2  | CDE  |  M  
3  | FGH  |  M


table 2

id | name | gender  
4  | BAC  |  F  
5  | DCE  |  F  
6  | GFH  |  F


how to make output in oracle database like this :

id | name | gender  
1  | ABC  |  M  
2  | CDE  |  M  
3  | FGH  |  M  
4  | BAC  |  F  
5  | DCE  |  F  
6  | GFH  |  F
Barbaros Özhan
  • 59,113
  • 10
  • 31
  • 55
harry
  • 9
  • 5

2 Answers2

3

Use UNION [ALL]:

select * from table1
union all
select * from table2;

P.S. If there exists any duplicated row for individual SELECT statements, UNION would remove duplicates, but UNION ALL concatenates rows even they are duplicates.

Barbaros Özhan
  • 59,113
  • 10
  • 31
  • 55
  • Thak you sir #Barbaros Ozhan – harry Apr 16 '18 at 10:35
  • 1
    Just to clarify: `union` concats the results and removes double entries. `union all` concats all results and doesnt remove duplicates. In your special given case, `union` would yield the same result. – Chrᴉz remembers Monica Apr 16 '18 at 10:48
  • @Chriz ok, thank you, What you told is true, but in this case both option may be used, since there's no repeated row. I took [ ALL ] in brackets, thank you again. – Barbaros Özhan Apr 16 '18 at 10:52
  • @BarbarosÖzhan... I would recommend to use `col1, col2, col3,.. coln` instead of `select *`. – Yogesh Sharma Apr 16 '18 at 10:53
  • @BarbarosÖzhan... How you know the two table has same structure (i.e. same no of columns) . It might be 1 table has 2 cols and other has only 1 col. – Yogesh Sharma Apr 16 '18 at 10:56
  • @YogeshSharma ok, that would be the choice, but for the case given in this Question, there's no problem – Barbaros Özhan Apr 16 '18 at 11:03
  • @YogeshSharma you cant even union tables if the dont got the same number of columns and same type per column. Therefor you have to preserve the right order, if they got columns of the same data type exchanged. – Chrᴉz remembers Monica Apr 16 '18 at 11:40
2

If you really need to "join" 2 tables:

with a as (
  select 1 id, 'ABC' name, 'M' gender from dual union all
  select 2 id, 'CDE' name, 'M' gender from dual union all
  select 3 id, 'FGH' name, 'M' gender from dual ), 
b as (
  select 4 id, 'BAC' name, 'F' gender from dual union all
  select 5 id, 'DCE' name, 'F' gender from dual union all
  select 6 id, 'GFH' name, 'F' gender from dual )
select coalesce(a.id, b.id) id,
       coalesce(a.name, b.name) name,
       coalesce(a.gender, b.gender) gender
  from a
  full join b
    on a.id = b.id
    /* if name, gender not in pk */
--   and a.name = b.name
--   and a.gender = b.gender
;

In this case all duplicated "ID"s will be removed. And first not null value of "name", "gender" columns will be returned becouse of coalesce function.

You can even use greatest, least and ets, instead of coalesce..

p.s. Be careful if you don't have PK on table!

Mikhail Kazantsev
  • 31
  • 2