COMPARISON Predicate
Function
This predicate performs a comparison between two expressions and returns a tri-state boolean result.
Invocation
One of the six predicates which constitute a search term.
Syntax
expression_1 [=, <>, >, >=, <, <=] [ | ANY | ALL | SOME |] expression_2
where
expression 1 | |
operator | One of the possible operators which must be chosen in order to perform the desired comparison. |
expression 2 | |
query specification | Contained in parentheses and may be given instead of the expression 2 . |
ALL, ANY, SOME | Keywords which can be used to transform the comparison expression from unquantified to quantified. |
Description
Operands, expressions, and query specifications must have a comparable data type. Should either of the expressions evaluate to NULL, then the predicate returns the tri-state value of UNKNOWN.
Expression 2 may be a query specification instead of a valid expression. This kind of query specification is a subquery or a subselect and has a cardinality of one. Since the data types must be comparable, the subquery may only specify one resultant column in its derived column list.
When used within an unquantified COMPARISON predicate, the resultant table may only return one value, thus `mimicking' a normal expression. If the subquery produce more than one result, or no result at all, it will return a runtime error. This cannot be checked at compilation time. Should the query return a NULL value, then the predicate equates to unknown.
The operator specifies the actual comparison operation to be performed. There are various alternative representations for the operators, depending upon which mode is current, as shown below.
A predicate is quantified if the subquery contains the keywords ALL, ANY, or SOME. The subquery can return more values; it is no longer restricted to zero or one. When you use ALL, the predicate equates to true if the comparison with expression 1 is true for all values returned by the subquery. When you use ANY, only one of the comparisons need be true for the predicate to be true. The keyword SOME is equivalent to ANY.
Should any particular value equal NULL, then the predicate returns the value UNKNOWN.
Strings can also be greater or less than other strings. For example `Swindon' < `Swinton' would equate to true.
Limitations
If a subquery is used in an unquantified comparison predicate, the subquery cannot contain a GROUP BY or HAVING clause; this would violate the requirement to return just one value. The subquery may not reference a grouped view as its source table.
ANSI Specifics
ANSI only allows the following operator representations:
= > < <> <= >=
Examples
If used within an unquantified COMPARISON predicate, the subquery must only return one result. The following example selects cruises which are less expensive than the price for a cruise with Yacht ID 145.
SELECT cruise_id, destination_harbor, cruise_price
FROM cruise
WHERE cruise_price <
( SELECT MIN (cruise_price)
FROM cruise
WHERE id_yacht=145);
If used within a quantified COMPARISON predicate, the subquery may return more than one result. The following describes the step-by-step processing of a query with the respective intermediate resultant tables. The example uses a base table named T1 with columns named a, b, c and d and T2 with columns named e, f and g. The apparent ordering of the intermediate resultant tables is due to ease of representation rather than of any predetermined ordering of the resultant tables.
SELECT a,d
FROM T1
WHERE b < ALL
(SELECT e
FROM T2
WHERE f = 10);
1. Step 1
CONNX establishes an intermediate resultant table containing all columns and all rows as defined in the table list for T1. For each row of IRT I, the subquery is evaluated and as described in Query Specification; IRT III is established from IRT II. This step is then performed for each occurrence of a row in IRT 1, as the result of the subquery may depend on values contained in IRT I. This occurs when the subquery contains an outer reference in its search condition.
2. Step 2
During this step, the subquery has been established as an intermediate resultant table (IRT) III. The comparison can now take place.
3. Step 3
During this step, all rows of T1 containing a value in column b which is smaller than ALL values in column e of T2 qualify for the intermediate resultant table IV which is the final result of the query.
