views:

38

answers:

2

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.

+2  A: 

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
"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
@Shannon Severance: I don't see the harm in suggesting a review of logic, or giving additional information that might be helpful.
OMG Ponies
@OMG Ponies: 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
+1  A: 
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
+1: I forgot to mention the columns at creation
OMG Ponies
@OMG Ponies: That bit me recently, it's fresh in the memory banks.
Shannon Severance