Sub Queries

Sub Queries OR Nested Select OR Sub Select OR Inner Select:
Ø  A sub query answers multiple-part questions.
Ø  A sub query in the where clause of a select statement is called as nested sub query.
Ø  A sub query in the FROM clause of a select statement is called as inline view.
Ø  A sub query can be part of a column, in the select list.
Ø  A sub query can contain another sub query.
Ø  Oracle imposes no limit on the number of sub query levels in from clause of the top-level query.
Ø  Within the where clause up to 255 sub quires can be nested.
Ø  To make the statements easier for readability, quality the columns in a sub query with the table name or table alias.
Purpose of a Sub Query:
Ø  To define the set of rows to be inserted into the target table of an INSERT or create table statement.
Ø  To define the set of rows to be included in a view or a materialized view in a create view or create materialized view statement.
Ø  To define one or more values to be assigned to exiting rows in an update statement.
Ø  To Provide values for conditions in a where clause, having clause, start with clause of select, update, and delete statements.
Ø  To define a table to be created on by a containing query.
Sub Query Principle:
Ø  Solve a problem by combining the two queries, placing one query inside the other query.
Ø  The inner query or the sub query returns a value that is used by the outer query upon the main query.

Sub Query Usage:
Ø  They are practically very useful when we need to select rows from a table with a condition that depends on the data in the table itself.
Syntax:
Ø  Select selectlist From TableName Where ColumnName Operator (
                                    Select select list From TableName
                        );
Ø  The Expressional operators in sub queries can be categorized into
            Single row Operators à =, <>, <, >, >=, <=

            Multiple Row Operators à IN, ANY, ALL