21

Is it possible to grab data from two tables (that have the same fields) into one view. Basically, so the view sees the data as if it was one table.

OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
Marcin
  • 1,266
  • 3
  • 15
  • 31

2 Answers2

36

Yes, using a UNION - 

CREATE VIEW vw_combined AS
   SELECT * FROM TABLE1
   UNION ALL
   SELECT * FROM TABLE2

...requires that there be the same number of columns, and the data types match at each position.

..preferrably, using a JOIN:

CREATE VIEW vw_combined AS
   SELECT * 
    FROM TABLE1 t1
    JOIN TABLE2 t2 ON t2.col = t1.col

But I want to warn against depending on views - if not materialized, they are only prepared SQL statements. There's no performance benefit, and can negatively impact performance if you build a view based on another. Also, views are brittle - they can change, and you won't know until using a supporting view if there are issues.

OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
  • 4
    "preferrably, using a JOIN" Only if that meets the requirements. I didn't see an implication that the rows in table1 and table2 were related in the way a join would make sense. OP did state: "that have the same fields" so the union would work if the types match up. – Shannon Severance Jul 16 '10 at 17:32
  • 1
    @Shannon Severance: I don't see the harm in suggesting a review of logic, or giving additional information that might be helpful. – OMG Ponies Jul 16 '10 at 17:37
  • 4
    I agree it was good to mention the join, because there are two ways to combine two tables making the results longer or wider than the tables by themselves. (+1 for that BTW.) I just don't think one is preferrable to the other without knowing for certain what OP is trying to do. – Shannon Severance Jul 16 '10 at 17:50
2
create or replace view view_name as
select * from table_1
union all select * from table_2

Note: The columns in the view are set at the time the view is created. Adding columns to table_1 and table_2 after view creation will not show up in view_name. You will need to re-run the above DDL to get new columns to show up.

If you want duplicate rows to be collasped to single rows (but potentially more work for the server):

create or replace view view_name as
select * from table_1
union select * from table_2

Generally it is bad form to use * in the select list, but assuming that the queries using the view are going to choose just what they need, I would use it here instead of explicitily naming all the columns. (Especially since I wouldn't want to have to add the column names when table_1 and table_2 change.)

Shannon Severance
  • 18,025
  • 3
  • 46
  • 67