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: 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
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.
Because the result set from an inline view is referenced by other elements of the containing query, we must ...