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;

Name	Null?	Type
EMPNO	NOT NULL	NUMBER(4)
ENAME		VARCHAR2(10)
DEPTPNO		NUMBER(2)
DNAME		VARCHAR2(14)

Sql> select * from t1_view;

EMPNO	ENAME	DEPTNO	DNAME
7369	SMITH	20	RESEARCH
7499	ALLEN	30	SALES
7521	WARD	30	SALES
7566	JONES	20	RESEARCH
7654	MARTIN	30	SALES

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.