When a SubQuery is used in a FROM clause instead of a Table name, Oracle executesthe subquery and then uses the resulting rows as a view in the FROMclause. It’s a very useful method for performing reports with various types of counts.

Consider the following classic query, which counts the number of awards accepted or rejected by employees. It does an outer join to take care ofemployees who might have only accepted or only rejected awards. This willgive one row per employee in an emp_status table:

 select a.id, nvl(accepted, 0)  TotAccepted, 
nvl(rejected, 0) TotRejected,fnamefrom emp_status  a, (select count(*) accepted, id,  
code , status               from  emp_status               where code  = 'AWARD'                and status = 'A'               group by id, code, status ) b ,               (select count(*) rejected, id,  
code , status               from   emp_status                where code = 'AWARD'               and status = 'R'               group by id, code, status) c ,               (select id, fname               from employee) dwhere a. code = 'AWARD'and a.status in ('R', 'A')and a.id = b.id(+)and a.id = c.id(+)and a.id = d.idgroup by a.id, accepted, rejected, fname