Re: SQL : In-line select statements

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Tue, 10 Aug 1999 12:51:57 GMT
Message-ID: <37bd1f83.89434429@newshost.us.oracle.com>

A copy of this was sent to "Martin Hoerchner" <Martin_at_mhoerch.demon.co.uk> (if that email address didn't require changing) On Mon, 9 Aug 1999 19:23:21 +0100, you wrote:

>Oddly enough, I have never found any documentation on the use of in-line
>select statements (also called in-line views), i.e. the construct
>
>select a.contact_name,
> c.customer_name
>from customers c,
> (select .. etc etc)
>
>Can anyone recommend a book which it? My main question is, when and why
>would you use it? What's the advantage?
>
>Any help would be gratefully received.
>
>Martin Hoerchner
>
>

Its documented in the SQL Language Ref from 7.2 of the database on.

When and Why would you use it? I use it when it

makes the query easier to write
when a query involved aggregates from >1 table and trying to do it in one query would be impossible otherwise (well, not impossible but really really hard). for example, lets say you wanted to write a query that shows: a department the sum of employees the number of projects being worked on

there are three tables DEPT, EMP, PROJ. emp and proj have foreign keys back to dept. with an inline view we can code:

select deptno, emp_cnt, proj_cnt
from dept, ( select deptno emp_dept, count(*) emp_cnt

                  from emp 
                 group by deptno ),
              ( select deptno proj_dept, count(*) proj_cnt
                 from proj
                group by deptno )

where deptno = emp_dept and deptno = proj_dept /

Whats the advantage? without it, I would have to create at least one 'real' view to do the above easily.....

--
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Tue Aug 10 1999 - 07:51:57 CDT