Chapter 16: Quantified Subquery Predicates
Overview
Quantifier is a logical operator that states the quantity of objects for which a statement is TRUE. This is a logical quantity, not a numeric quantity; it relates a statement to the whole set of possible objects. In everyday life, you see statements like "There is only one mouthwash that stops dinosaur breath," "All doctors drive Mercedes," or "Some people got rich investing in cattle futures," which are quantified.
The first statement, about the mouthwash, is a uniqueness quantifier. If there were two or more products that could save us from dinosaur breath, the statement would be FALSE. The second statement has what is called a universal quantifier, since it deals with all doctors find one exception and the statement is FALSE. The last statement has an existential quantifier, since it asserts that one or more people exist who got rich on cattle futures find one example and the statement is TRUE.
SQL has forms of these quantifiers that are not quite like those in formal logic. They are based on extending the use of comparison predicates to allow result sets to be quantified, and they use SQL's three-valued logic, so they do not return just TRUE or FALSE.
16.1 Scalar Subquery Comparisons
Standard SQL allows both scalar and row comparisons, but most queries use only scalar expressions. If a subquery returns a single-row, single-column result table, it is...