Re: Select In From Clause
Jeremy Rickard wrote:
>
> In article <5onbvc$a0t@masters0.InterNex.Net>, Chez David
> <dmitchell@SagentTech.com> writes
>
> >Anyone know if Informix ( 7.x ) supports SQL of this form? SQL Server and
> >Oracle seem to.
> >
> >SELECT
> >T0.C0, T1.C0
> >FROM
> >( SELECT JOB_ID AS C0 FROM JOBS ) T0,
> >( SELECT JOB_ID AS C0 FROM EMPLOYEE ) T1
> >WHERE
> >T0.C0 = T1.C0
>
> Does anyone want it to?!
>
> Personally, I think this could lead to some very ugly SQL queries. Can
> you think of any examples that couldn't be re-written as a "normal"
> query without looking at least as elegant?
>
> If this is really needed then I think it would be "prettier" to define
> T0 and T1 as temporary tables (using WITH or whatever construct
> is/becomes the standard for this).
>
> What's the general opinion on this form?
> --
> Jeremy Rickard
That syntax is standard SQL92 syntax for derived tables. It is indeed
quite useful for expressing queries that do:
- double aggregations:
SELECT AVG (CNT) AVG_EMPS_PER_DEPT
FROM
(SELECT COUNT(*) CNT, DEPARTMENT_CODE
FROM EMPLOYEES
GROUP BY DEPARTMENT_CODE) DEPT_CNTS;
- joining aggregations
SELECT E_CNT, P_CNT, E.DEPARTMENT_CODE
FROM
(SELECT COUNT(*) E_CNT, DEPARTMENT_CODE
FROM EMPLOYEES
GROUP BY DEPARTMENT_CODE) E,
(SELECT COUNT(*) P_CNT, DEPARTMENT_CODE
FROM PROJECTS
GROUP BY DEPARTMENT_CODE) P
WHERE E.DEPARTMENT_CODE = P.DEPARTMENT_CODE;
Without derived tables, those queries would need to be expressed using
views.
/albert
--
Albert Godfrind Oracle Rdb Engineering
Oracle Corporation Email: agodfrin@fr.oracle.com
DEC European Technical Center godfrind@heron.enet.dec.com
950 Route des Colles Phone: +33/4/92.95.51.63
06901 Sophia-Antipolis Mobile: +33/6/09.97.27.23
France FAX: +33/4/92.95.50.50