Suppose we have EMP and DEPT table. To see the empno, ename, sal, deptno, department name and location we have to give a join query like this.
From emp e, dept d where e.deptno=d.deptno;
So everytime we want to see emp details and department names where they are working we have to give a long join query. Instead of giving this join query again and again, we can create a view on these table by using a CREATE VIEW command given below
create view emp_det
from emp e JOIN dept d ON e.deptno=d.deptno;
Now to see the employee details and department names we don’t have to give a join query, we can just type the following simple query.
select * from emp_det;
This will show same result as you have type the long join query. Now you can treat this EMP_DET view same as any other table.
For example, suppose all the employee working in Department No. 10 belongs to accounts department and most of the time you deal with these people. So every time you have to give a DML or Select statement you have to give a WHERE condition like .....WHERE DEPTNO=10. To avoid this, you can create a view as given below