Tutorial :Data from two tables into one view



Question:

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.


Solution:1

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.


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


Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »