INLINE VIEW
- It is not a schema object like a normal view.
- It is sub query with a name (alias) placed in the from clause of another select statement (main query) for which it (the sub query) acts as a data source.
- The outer query will have a reference of the inline view.
- The inline view can have a GROUP BY clause, order by clause or even inline view itself can be join.
- Inline views are useful for performing the Top-N (Top 3 sales reps or top 10 students etc) analysis.
See AN EXAMPLE OF
INLINE VIEW, WHICH HAS THE GROUP BY CLAUSE.
The query finds the employees in the emp table whose salary is less than
the maximum salary of their department.
SQL> SELECT ENAME,SAL,E1.DEPTNO,E2.MAXSAL FROM EMP E1,
(SELECT DEPTNO,MAX(SAL)
MAXSAL FROM EMP GROUP BY DEPTNO)E2
WHERE
E1.DEPTNO=E2.DEPTNO AND E1.SAL<E2.MAXSAL;
ENAME SAL DEPTNO
MAXSAL
----------
---------- ---------- ----------
CLARK 2450 10 5000
MILLER 1300 10 5000
SMITH 800 20
3000
ADAMS 1100 20
3000
JONES 2975 20 3000
ALLEN 1600 30
2850
MARTIN 1250 30
2850
JAMES 950 30
2850
TURNER 1500 30 2850
WARD 1250 30 2850
10 rows
selected.
SELECT ENAME,SAL,E1.DEPTNO,E2.MAXSAL FROM EMP E1,
(SELECT DEPTNO,MAX(SAL)
MAXSAL FROM EMP GROUP BY DEPTNO order by deptno)E2 WHERE E1.DEPTNO=E2.DEPTNO AND
E1.SAL<E2.MAXSAL;
ENAME SAL DEPTNO
MAXSAL
----------
---------- ---------- ----------
CLARK 2450 10 5000
MILLER 1300
10 5000
SMITH 800
20
3000
ADAMS 1100
20 3000
JONES 2975 20 3000
ALLEN 1600
30 2850
MARTIN 1250
30 2850
JAMES 950
30 2850
TURNER 1500
30 2850
WARD 1250
30 2850
10 rows
selected.
No comments:
Post a Comment