Creating View from Another View Creating View from Another View sql sql

Creating View from Another View

You can certainly have a view that's built on top of another view:

create table my_table (id number, name varchar2(20), address varchar2(30));table MY_TABLE created.create or replace view my_view_1 asselect id, namefrom my_table;view MY_VIEW_1 created.create or replace view my_view_2 asselect,, mt.addressfrom my_view_1 mv1join my_table mt on =;view MY_VIEW_2 created.

But you can't reference anything in the underlying tables, including any fields that are not part of the view:

create or replace view my_view_3 asselect,, mv1.addressfrom my_view_1 mv1join my_table mt on =;SQL Error: ORA-00904: "MV1"."ADDRESS": invalid identifier00904. 00000 -  "%s: invalid identifier"

The underlying tables having the same columns isn't an issue, and if you include more than on in the view then you'd have to alias them anyway.

You can sometimes get performance issues doing this, and it might be faster and more reliable - though possibly harder to maintain - if you create your new view against the same base tables, and expand it to include the extra data you want.

Long answer ...

It's one of the fundamental characteristics of a relational database that there should be no logical difference between tables, queries results, and views (which are simply stored queries). In fact the "relational" refers to the rows of data that are accessible through any one of these.

There are of course differences imposed to different degrees by different RDBMSs, particularly when it comes to DDL commands (update, delete, insert), and they all impose restrictions on the kinds of object that DDL can be applied to.

Taking Oracle as an example, the system will allow updates and deletes on key-preserved views, and inserts are possible but rarely used in practice (an "instead of" trigger type is available to allow DDL against any view).

So given all that, you can run a select against:

  1. A table
  2. A set of joined tables
  3. A view
  4. A query (commonly referred to as an in-line view)
  5. A query joined to a view and a table
  6. etc

... and that select can therefore be encapsulated in a view definition.

Short answer: Yes