Inline view

From Oracle FAQ
Jump to: navigation, search

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).

[edit] Examples

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.

[edit] Also see

  • Query - a SELECT statement
  • Subquery - don't confuse inline views with subqueries!
Glossary of Terms
A B C D E F G H I J K L M N O P Q R S T U V W X Y Z #