Combining Tables

[ejgb]

I have two tables with the exact same structure, but contain different data, in two seperate schemas. I want to create a view so that too the users it looks like they are looking at one table.

 

[taupirho]

create view myview as
select col1,col2 ... from table1
union
select col1, col2 ... from table2

 

[carp]

If the data is truly different (that is, no rows with common values) or duplicate rows are acceptable, then

CREATE VIEW my_view AS
SELECT * FROM table1
UNION ALL
SELECT * FROM table2;

will be more efficient, since this avoids sorting the data before displaying it.