Views are subset of table. View also has set of records in the form of rows and columns. But it is created based on the records in one or more tables. A query is written on table/s and is given a name. Such named query is called as a view.
A table will have large number of data and table will be fired with specific frequently. In such case, instead of rewriting the query again and again, a name is given to the query and it will be called whenever it is required. Hence view is also called as named query or stored query. A view can be created as follows :
CREATE OR REPLACE VIEW view_name AS SELECT COLUMN1, COLUMN2 …COLUMNN FROM table_name WHERE [Condition];
Here table_name can be one or more tables and condition can have joins as well as filtering conditions to select the subsets. For example, create a view on table Pen with Red color ink.
CREATE OR REPLACE VIEW vw_RedInkPen AS SELECT * FROM Pen -- selects all the columns into view WHERE INK_COLOR = ‘Red’;
Example of view with more than one table :
CREATE OR REPLACE VIEW vw_Design_Emp AS SELECT e.EMP_ID, e.EMP_FIRST_NAME, e.EMP_LAST_NAME, d.DEPT_ID, d.DEPT_NAME FROM EMPLOYEE e, DEPARTMENT d – two tables are used to create a view WHERE e.DEPT_ID = d.DEPT_ID -- Join condition AND d.DEPT_NAME = ‘DESIGN’; -- Filtering condition
A view can be queried same way a table is queried – by using SELECT. We can even have conditions, sorting, grouping, relation operator etc like we do in tables.
SELECT * FROM vw_RedInkPen; SELECT * FROM vw_Design_Emp WHERE EMP_ID = 12121;
When we create a view, only the query used to create the view is stored with view name. it does not create any copy of data in a separate file. In other words, views are virtual tables. When we fire query on these views, the underlying view query will be executed. Hence it does not consume any space in memory to store the data nor does it create same copies of data in the database. It simply shows the records from the table itself. Below diagram depicts this idea clearly.
The basic advantage of view is it simplifies the query. It replaces the large and frequently used query as single table and allows querying it as any other table. In addition to this, we can give different accesses to different users on this view like we do it on table. When we give control access on the view, users will have access on only the portion of the table. Suppose we have created vw_Design_Emp and vw_Test_Emp view and given SELECT grants to the Design employees on vw_Design_Emp and Test employees on vw_Test_Emp. Now design employees will not be able to see the content of vw_Test_Emp view and vise versa. We can see the same in below diagram. Even though users are given access on the views, they will not have access on the EMPLOYEE and DEPT tables. They will have access only on the columns and rows which represent the view. For example, ADDRESS column in EMPLOYEE table is not part of any view and Design or Testing employees will not be able see the ADDRESS column value.
Note : in the above diagram two views are represented as two separate tables, but they are not two separate tables. The data of each view are still in the respective table. This is represented as above for understanding it in better way when tables are joined to create a view and their roles.
When we INSERT or DELETE the records from the view, records from the table will be deleted provided it does not violate the integrity rules of table/s. When we update the view, it update the table with which it is being created. But we cannot update the view for computed columns or any column values in a view based on UNION query.
We can drop the view by writing:
DROP VIEW view_name; DROP VIEW vw_Test_Emp;