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.