Oracle SQL Sub-Query

Subquery is a query inside a main query.

SQL subquery can be embedded:

• As a column expression in the main SQL statement
• As a filter inside the WHERE (or HAVING) clause in the main SQL statement
• As a datasource inside the FROM clause in the main SQL statement

While working with SQL Subqueries you must:

• Enclose the subquery in parentheses
• Do not use a semicolon at the end of the subquery statement

Using Subqueries

A subquery answers multiple-part questions. For example, to determine who works in Taylor's department, you can first use a subquery to determine the department in which Taylor works. You can then answer the original question with the parent SELECT statement. A subquery in the FROM clause of a SELECT statement is also called an inline view. A subquery in the WHERE clause of a SELECT statement is also called a nested subquery.

A subquery can contain another subquery. Oracle Database imposes no limit on the number of subquery levels in the FROM clause of the top-level query. You can nest up to 255 levels of subqueries in the WHERE clause.

If columns in a subquery have the same name as columns in the containing statement, then you must prefix any reference to the column of the table from the containing statement with the table name or alias. To make your statements easier to read, always qualify the columns in a subquery with the name or alias of the table, view, or materialized view.

Oracle performs a correlated subquery when a nested subquery references a column from a table referred to a parent statement any number of levels above the subquery. The parent statement can be a SELECT, UPDATE, or DELETE statement in which the subquery is nested. A correlated subquery is evaluated once for each row processed by the parent statement. Oracle resolves unqualified columns in the subquery by looking in the tables named in the subquery and then in the tables named in the parent statement.

A correlated subquery answers a multiple-part question whose answer depends on the value in each row processed by the parent statement. For example, you can use a correlated subquery to determine which employees earn more than the average salaries for their departments. In this case, the correlated subquery specifically computes the average salary for each department.

Example 1 of subquery

Find the names of all of the managers in the Employees table.

Employees (emp_id, Emp_name, ManagerID)

SELECT Emp_name AS Employee
FROM Employees
WHERE emp_id IN (SELECT DISTINCT ManagerID FROM Employees)

Example 2 of SQL Subquery

Store_Info (store_name, sales, date)
Location (region_names, store_name)

Find the sales of all stores in the East region.

SELECT SUM(Sales) FROM Store_Info
WHERE Store_name IN
(SELECT store_name FROM Location
WHERE region_name = 'East')

You can nest any number of such queries; Oracle does not have a limit. 

There is also another term used NESTED SUBQUERIES. when you use subqueries in the WHERE clause of the SELECT statement it is called nested subquery. There are only 255 levels of subqueries.