Database Reference
In-Depth Information
Note:
1.
Several layers of nesting may be constructed; however, this is
not recommended.
2.
Within nested queries, a scalar variable is confined (known
only) to its select block at each level. However, (scalar)
variables within a higher block, A, are known to the inner
blocks of A. For instance, if a query solution consists of blocks
A, B, C, where A consists of B and C, then:
•
Variables of block C are not known to block B and vice versa.
•
Variables of blocks B and C are not known to block A.
•
Variables of block A are known to blocks B and C.
Query Using Exists and NOT Exists
SQL also allows the specification of nested queries that involve the use of the existential
quantifiers (EXISTS and NOT EXISTS). As mentioned earlier, the universal quantifier is
not supported in some implementations of the language (it is supported in Oracle 10G and
Oracle 11G). However, considering the standardization rules of Chapter 8 (section 8.4),
this is not a serious deterrent. Moreover, in most cases, the rule of not retrieving a
bounded tuple variable, along with its consequences, ensure that SQL statements can be
specified without the use of quantifiers. That said, SQL nonetheless supports the use of the
existential quantifiers, albeit in a manner that is slightly different from what was discussed
in Chapter 8: the tuple variable is usually implicit.
Example 43:
Let us revisit the problem of determining program names of programs
that include the course M100:
