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