Using Subqueries in Oracle

This article is written about how to use Oracle subqueries with syntax and examples.

What is a subquery in Oracle?

In Oracle, a subquery is a query inside a query. You can create subqueries inside your SQL statements. These subqueries can stay in the WHERE clause, the FROM clause, or the SELECT clause.

WHERE clause

Most often, the subquery will be discovered in the WHERE clause. These subqueries are also referred to as 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');

Limitation: Oracle lets in up to 255 ranges of subqueries in the WHERE clause.

FROM clause

A subquery can also be determined in the FROM clause. These are known as 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 title subquery1. This will be the title used to reference this subquery or any of its fields.

Limitations

Oracle allows an unlimited quantity of subqueries in the FROM clause.

SELECT clause

A subquery can also be located 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 got 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 title subquery2. This will be the identify used to reference this subquery or any of its fields.

The trick to placing a subquery in the select clause is that the subquery have to return a single value. This is why an combination function such as SUM function, COUNT function, MIN function, or MAX feature is commonly used in the subquery.