Re: Select In From Clause
In article <33B144B5.3EB36D6F@fr.oracle.com>, Albert Godfrind
<agodfrin@fr.oracle.com> writes
>Jeremy Rickard wrote:
[snip]
>> >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?!
[snip]
>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;
True, but I just feel it reads more clearly with a view or common table
expression (aka temporary view).
I've seen some very ugly queries with "standard SQL". Imagine what some
people will do with these new toys!
--
Jeremy Rickard