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 subqueries 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;
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)
FROM all_tables tbls;
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.
Home 