Mastering Oracle SQL

Inline Views

Most texts covering SQL define the FROM clause of a SELECT statement as containing a list of tables and/or views. Please abandon this definition and replace it with the following: 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:^[10] 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;

   DEPT_ID NAME                        TOT
---------- -------------------- ----------
         1 Human Resources               1
         2 Accounting                    1
         3 Domestic Sales               19
         4 International Sales           5

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.

Inline View Basics

Because the result set from an inline view is referenced by other elements of the containing query, we must ...