SUBQUERIES

A subquery is best defined as a query within a query. It is the use of a SELECT statement inside one of the clauses of another SELECT statement. A subquery can also be nested inside INSERT, UPDATE, and DELETE statements. Subqueries must be enclosed within parentheses. Subquery can be placed either in FROM clause, WHERE clause or HAVING clause of the main query. Oracle allows a maximum nesting of 255 subquery levels in a WHERE clause. There is no limit for nesting subqueries expressed in a FROM clause.

Syntax of a subquery is:

( SELECT [DISTINCT] subquery_select_parameter

  FROM {table_name | view_name}

               {table_name | view_name} ...

  [WHERE search_conditions]

  [GROUP BY column_name [,column_name ] ...]

  [HAVING search_conditions] )

There are three broad divisions of subquery:

1)Single-row subqueries

2)Multiple-row subqueries

3)Correlated subqueries

Single Row Sub Query

The single-row subquery returns one row. A special case is the scalar subquery, which returns a single row with one column. 

Multiple Row Sub Query

Multiple-row subqueries return sets of rows. These queries are commonly used to generate result sets that will be passed to a DML or SELECT statement for further processing.

Correlated Sub Query

A Correlated Subquery is one that is executed after the outer query is executed. So correlated subqueries take an approach opposite to that of normal subqueries.