Tuesday 4 September 2012

What is an Inline View?

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

Related Posts Plugin for WordPress, Blogger...

ShareThis