Database Reference
In-Depth Information
In this format, the following rules apply:
1.
The sub-query is another
Select s
tatement, which cannot
include an
Order-By-Clause
.
2.
The inner query is executed first, and its result is passed to the
outer query.
3.
Within the
Where-Clause
, a scalar expression involving at
least one column may be specified (immediately following
WHERE) instead of a column.
An alternate format for a sub-query is:
In this format, the following rules apply:
1.
The relation-list includes at least one sub-query.
2.
It is possible to have this format combined with the first.
3.
The inner query is executed first, and its result is passed to the
outer query.
Example 38:
Produce a list showing student name and associated major (program)
name (assuming a student's program code is never null):
Example 39:
Referring to the Oracle employee-department database, produce a list
of employees who earn the maximum salary in their respective departments:
Note:
In queries of this form, do not use the
equal operator
(=) as the connecting
operator unless you are sure that the inner sub-query produces only one row. In any
event, the
IN operator
is safer.
