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.
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.
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.)