ALL, ANY, SOME Subquery:
(1) (2)
|--| Expression |-------| Relational Operator |----------------->
>--+------+--(--subquery--)-------------------------------------|
+-ALL--+
+-ANY--+
'-SOME-'
| Element | Description | Restrictions | Syntax |
|---|---|---|---|
| subquery | Embedded query | Cannot contain the FIRST or the ORDER BY clause | SELECT, p. SELECT |
Use the ALL, ANY, and SOME keywords to specify what makes the condition TRUE or FALSE. A search condition that is TRUE when the ANY keyword is used might not be TRUE when the ALL keyword is used, and vice versa.
The ALL keyword specifies that the search condition is TRUE if the comparison is TRUE for every value that the subquery returns. If the subquery returns no value, the condition is TRUE.
In the following example, the first condition tests whether each total_price is greater than the total price of every item in order number 1023. The second condition uses the MAX aggregate function to produce the same results.
total_price > ALL (SELECT total_price FROM items
WHERE order_num = 1023)
total_price > (SELECT MAX(total_price) FROM items
WHERE order_num = 1023)
Using the NOT keyword with an ALL subquery tests whether an expression is not TRUE for at least one element that the subquery returns. For example, the following condition is TRUE when the expression total_price is not greater than all the selected values. That is, it is TRUE when total_price is not greater than the highest total price in order number 1023.
NOT total_price > ALL (SELECT total_price FROM items
WHERE order_num = 1023)
The ANY keyword denotes that the search condition is TRUE if the comparison is TRUE for at least one of the values that is returned. If the subquery returns no value, the search condition is FALSE. The SOME keyword is a synonym for ANY.
The following conditions are TRUE when the total price is greater than the total price of at least one of the items in order number 1023. The first condition uses the ANY keyword; the second uses the MIN aggregate function:
total_price > ANY (SELECT total_price FROM items
WHERE order_num = 1023)
total_price > (SELECT MIN(total_price) FROM items
WHERE order_num = 1023)
Using the NOT keyword with an ANY subquery tests whether an expression is not TRUE for all elements that the subquery returns. For example, the following condition is TRUE when the expression total_price is not greater than any selected value. That is, it is TRUE when total_price is greater than none of the total prices in order number 1023.
NOT total_price > ANY (SELECT total_price FROM items
WHERE order_num = 1023)
You can omit the keywords ANY, ALL, or SOME in a subquery if you know that the subquery will return exactly one value. If you omit the ANY, ALL, or SOME keywords, and the subquery returns more than one value, you receive an error. The subquery in the following example returns only one row because it uses an aggregate function:
SELECT order_num FROM items
WHERE stock_num = 9 AND quantity =
(SELECT MAX(quantity) FROM items WHERE stock_num = 9)
If you preface a condition with the keyword NOT, the test is TRUE only if the condition that NOT qualifies is FALSE. If the condition that NOT qualifies has a NULL or an UNKNOWN value, the NOT operator has no effect.
The following truth table shows the effect of NOT. Here T represents a TRUE condition, F represents a FALSE condition, and a question mark (?) represents an UNKNOWN condition. (An UNKNOWN value can occur when an operand is NULL).
The left column shows the value of the operand of the NOT operator, and the right column shows the returned value after NOT is applied to the operand.
You can combine simple conditions with the logical operators AND or OR to form complex conditions. The following SELECT statements contain examples of complex conditions in their WHERE clauses:
SELECT customer_num, order_date FROM orders
WHERE paid_date > '1/1/97' OR paid_date IS NULL;
SELECT order_num, total_price FROM items
WHERE total_price > 200.00 AND manu_code LIKE 'H%';
SELECT lname, customer_num FROM customer
WHERE zipcode BETWEEN '93500' AND '95700'
OR state NOT IN ('CA', 'WA', 'OR')
The following truth tables show the effect of the AND and OR operators. The letter T represents a TRUE condition, F represents a FALSE condition, and the question mark (?) represents an UNKNOWN value. An UNKNOWN value can occur when part of an expression that uses a logical operator is NULL.
The marginal values at the left represent the first operand, and values in the top row represent the second operand. Values within each 3x3 matrix show the returned value after the operator is applied to those operands.
If the Boolean expression evaluates to UNKNOWN, the condition is not satisfied.
Consider the following example within a WHERE clause:
WHERE ship_charge/ship_weight < 5 AND order_num = 1023
The row where order_num = 1023 is the row where ship_weight is NULL. Because ship_weight is NULL, ship_charge/ship_weight is also NULL; therefore, the truth value of ship_charge/ship_weight < 5 is UNKNOWN. Because order_num = 1023 is TRUE, the AND table states that the truth value of the entire condition is UNKNOWN. Consequently, that row is not chosen. If the condition used an OR in place of the AND, the condition would be TRUE.
For discussions of comparison conditions in the SELECT statement and of conditions with a subquery, see the IBM Informix Guide to SQL: Tutorial. For information on the GLS aspects of conditions, see the IBM Informix GLS User's Guide.