Inline Views

Most texts covering SQL define the FROM clause of a SELECT statement as containing a list of tables and/or views. In the following  query the FROM clause contains a list of data sets. In this light, it is easy to see how the FROM clause can contain tables (permanent data sets), views (virtual data sets), and SELECT statements (temporary data sets).

A SELECT statement in the FROM clause of a containing SELECT statement is referred to as an inline view it is one of the most powerful, underutilized features of Oracle SQL. Here’s a simple example:

SELECT d.dept_id, d.name, emp_cnt.tot

FROM department d, (SELECT dept_id, COUNT(*) tot  FROM employee  GROUP BY dept_id) emp_cnt WHERE d.dept_id = emp_cnt.dept_id;

 In this example, the FROM clause references the department table and an inline view called emp_cnt, which calculates the number of employees in each department. The two sets are joined using dept_id and the ID, name, and employee count are returned for each department. While this example is fairly simple, inline views allow us to do things in a single query that might otherwise require multiple select statements or a procedural language to accomplish.