On the move:

I'm currently moving the following to their own blogs: Unix, HTML for Blogs, Perl & Java.
The links are on the left side.

Sunday, June 22, 2008

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:

No comments:

Post a Comment