Re: SQL:In-line select statements

From: Mungo Henning <mungoh_at_itacs.strath.ac.uk>
Date: Thu, 12 Aug 1999 11:17:12 +0100
Message-ID: <37B29F28.9449AE9B_at_itacs.strath.ac.uk>


Martin Hoerchner 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?

Output someone's salary along with the average for their department:

select ename, sal, y.average
from emp e, (select avg(sal) average, deptno from emp group by deptno) y where e.deptno = y.deptno

You could just create a view, then join the table to the view, then delete the view, but the inline view does all this for you.

HTH Mungo

--
Mungo Henning - it's a daft name but it goes with the face...
mungoh_at_itacs.strath.ac.uk.http://www.itacs.strath.ac.uk/
I speak for me, not my employer.
Received on Thu Aug 12 1999 - 12:17:12 CEST

Original text of this message