O'Reilly logo

Mastering Oracle SQL by Alan Beaulieu, Sanjay Mishra

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required