In-line View

An in-line view is created when a Select statement is used in the From clause. This sets up a table that is there only until the query ends. The first and second examples of code produce exactly the same results.

The second example pulls only the data necessary from the individual tables before they are then joined with a parent Select.

Set Pagesize 60
Break on DeptNo on DName on Loc Skip 1



Select DeptNo, DName, Loc,
       EmpNo,  EName, Job
  From Emp
  Join Dept
 Using (DeptNo)
 Where Deptno != 30
  And Job in ('CLERK', 'ANALYST')
 Order By DeptNo;



Select DeptNo, DName, Loc,
       EmpNo,  Ename, Job
 From
      ------------------------------
      (
       Select Deptno, Dname, Loc
         From Dept
        Where DeptNo != 30
      ) 
      ------------------------------
 Join
      (
       Select Empno, Ename, Job, Deptno
         From Emp
        Where Job in ('CLERK', 'ANALYST')
      ) 
      ------------------------------
Using (Deptno)
Order by Deptno;

See Oracle documentation for further detail:

Subqueries & In-line Views