oracle tables in one view

Question

hi, i have 2 tables in an oracle database, which has the same column-names and types. for example:

Table1: id, name, comment Table2: id, name, comment

How can i show all data from both table in one view?

Answer 1

Why two identical tables? Whatever happened to "Don't Repeat Yourself"? Sorry, sounds like a bad design smell to me.

Whatever difference inspired you to create two tables, I'll bet it really could be another attribute to distinguish two groups in one table.

Answer 2

If you want 4 separate columns, simply use aliases, like you would any other select.

create or replace view vw_my_view as
  select t1.id t1_id
        ,t1.comment t1_comment
        ,t2.id t2_id
        ,t2.comment t2_comment
  from table1 t1
    inner join table2 t2 on join condition
  where filter conditions

EDIT Of course, your tables will relate to each other in some way, otherwise there is no way for a single row of your view to mean anything. You will therefore have a join condition to join the two tables, such as t1.id = t2.id

If you want them in two columns, use Union

create or replace view vw_my_view as
  select id
        ,comment
  from table1
  union all                -- use ALL unless you want to lose rows
  select id
        ,comment
  from table2;

Answer 3

SELECT * FROM TABLE1 UNION SELECT * FROM TABLE2

(or UNION ALL if you want duplicates)

Answer 4

I agree with what duffymo says, but if there is a good reason for it then a UNION will do it for you. e.g.

SELECT id, name, comment FROM Table1
UNION
SELECT id, name, comment FROM Table2

Answer 5

select * from table1
union
select * from table2;

Answer 6

thanks, works great!!!!