Subqueries

What is a subquery?

A subquery is a query within a query. In Oracle, you can create subqueries within your SQL statements. These subqueries can reside in the WHERE clause, the FROM clause, or the SELECT clause.

• WHERE clause

Most often, the subquery will be found in the WHERE clause. These subqueries are also called nested subqueries.

For example:

select * from all tables tabs
where tabs.table_name in	(select cols.table_name
	from all_tab_columns cols
	where cols.column_name = 'SUPPLIER_ID');

Limitations: Oracle allows up to 255 levels of sub queries in the WHERE clause.

• 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 sub-query 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.

Note: 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.