FROM clause
A subquery can also be found in the FROM clause. These are called inline views.
For example:
select suppliers.name, subquery1.total_amt
from suppliers,
(select supplier_id, Sum(orders.amount) as total_amt
from orders
group by supplier_id) subquery1,
where subquery1.supplier_id = suppliers.supplier_id;
In this example, we've created a subquery in the FROM clause as follows:
(select supplier_id, Sum(orders.amount) as total_amt
from orders
group by supplier_id) subquery1
This subquery has been aliased with the name subquery1. This will be the name used to reference this subquery or any of its fields.
Limitations: Oracle allows an unlimited number of subqueries in the FROM clause.
SELECT clause
A subquery can also be found in the SELECT clause.
For example:
select tbls.owner, tbls.table_name,
(select count(column_name) as total_columns
from all_tab_columns cols
where cols.owner = tbls.owner
and cols.table_name = tbls.table_name) subquery2
from all_tables tbls;
In this example, we've created a subquery in the SELECT clause as follows:
(select count(column_name) as total_columns
from all_tab_columns cols
where cols.owner = tbls.owner
and cols.table_name = tbls.table_name) subquery2
The subquery has been aliased with the name subquery2. This will be the name used to reference this subquery or any of its fields.
The trick to placing a subquery in the select clause is that the subquery must return a single value. This is why an aggregate function such as SUM, COUNT, MIN, or MAX is commonly used in the subquery.
Acknowledgements: We'd like to thank Peter for contributing to this solution!
|