Oracle SQL – Inline View

Last Updated on :September 29, 2019

• An Inline View is a SELECT statement written in the FROM clause of a query.
• Inline View are used in ORACLE SQL to simplify complex query by removing joins operation.
• Inline View allows you to do complex things in a single query
• Inline View are enclosed with in set of parentheses.

Example of Inline View

In below example inline view referenced with an alias ‘e’ which retrieves number of employees in each department.  This result set of inline view is joined with dept table using  deptno

SELECT ROWNUM slno,d.dname,d.loc,e.emp_count
FROM (SELECT deptno, COUNT (*) emp_count
FROM emp
GROUP BY deptno)e,
dept d
WHERE d.deptno = e.deptno;

Output

SLNO,DNAME,LOC,EMP_COUNT
1,"ACCOUNTING","NEW YORK",3
2,"RESEARCH","DALLAS",5
3,"SALES","CHICAGO",6