RSS Feed

SQL Join View

SQL Join View

A join view is a view which is based on more than one base table. Any insert, update or delete statement on a view can update only one of the base tables involved in the view. A table is called a key preserved in a join view, if the primary and unique keys are unique on the views result set.

Sql> create view t1_view as select a.empno, a.ename, a.deptno, b.dname
2 from emp a, dept b
3 where a.deptno = b.deptno;
View created.

Sql>desc t1_view;

NameNull?Type
EMPNONOT NULLNUMBER(4)
ENAME
VARCHAR2(10)
DEPTPNO
NUMBER(2)
DNAME
VARCHAR2(14)


Sql> select * from t1_view;

EMPNOENAMEDEPTNODNAME
7369SMITH20RESEARCH
7499ALLEN30SALES
7521WARD30SALES
7566JONES20RESEARCH
7654MARTIN30SALES

5 rows selected.

In the above view table emp is key preserved because primary key of the emp is also unique in the view. The dept table is not key preserved as it primary key deptno is duplicated many times.

You can update only the key preserved table through the view.

If the view is defined with the WITH CHECK OPTION then you cannot update columns that join the base tables.

Insert statements also cannot refer to any column of the non key preserved table. If a view is created with “WITH CHECK OPTION” , then no insert is permitted on view.