An inline view is a SELECT statement in the FROM-clause of another SELECT statement. In-line views are commonly used to simplify complex queries by removing join operations and condensing several separate queries into a single query. This feature was introduced in Oracle 7.2.
This feature is commonly referred to in the MSSQL community as a derived table, and in the Postgres community simply refers to it as a subselect (subselects are inline views + subqueries in Oracle nomenclature).
Example inline view:
SELECT * FROM ( SELECT deptno, count(*) emp_count FROM emp GROUP BY deptno ) emp, dept WHERE dept.deptno = emp.deptno;
Another good example of an inline view is:
SELECT a.last_name, a.salary, a.department_id, b.maxsal FROM employees a, ( SELECT department_id, max(salary) maxsal FROM employees GROUP BY department_id ) b WHERE a.department_id = b.department_id AND a.salary = b.maxsal;
The above query display the employees who earn the highest salary in each department.
|Glossary of Terms