IN Predicate
Function
This predicate tests whether a given value is contained within a specified set of values and returns a tri-state boolean result.
Invocation
One of the six predicates which constitute a search term.
Syntax
expression_1 [NOT] IN ( [QUERY specification | expression_n [,])
where
expression | A valid expression as described in the section Expressions. |
NOT | An operator which negates the effect of the membership test. |
host variable specification | A valid single host variable specification and its value specifies a set member. |
query specification | Contained in parentheses and may be given instead of an explicit list separated by commas. |
constant | A valid constant and its value specifies a set member |
Description
The IN predicate is a search expression containing comparison predicates linked by the OR operator.
CONNX processes the IN predicate the following ways:
x IN ( 1,2,3 )
x = 1 OR x = 2 OR x = 3
x IN ( subquery )
x = ANY ( subquery )
The expression and all members of the set, no matter whether they are explicit or returned as the result of the subquery, must have comparable data types. If the expression or any of the set members evaluate to the NULL value, the predicate returns the tri-state value of unknown.
The query specification follows the rules for subqueries within a quantified COMPARISON predicate. The subquery may only specify one resultant column in its derived column list, although it may return many different values/rows.
String comparison follows the same rules as specified for the COMPARISON predicate.
ANSI Specifics
In ANSI compatibility mode, the special USER register is not supported.
CONNX Specifics
None. SQL 2 Standard allows query expressions and list of expressions as set.
Examples
The following example selects all skippers who are on cruises starting from BAHAMAS, PANAMA or TRINIDAD:
SELECT id_skipper
FROM cruise
WHERE start_harbor IN ( 'BAHAMAS', 'PANAMA', 'TRINIDAD' );
The following example identifies all customers who will be starting a cruise from MIAMI:
SELECT id_customer
FROM contract
WHERE id_cruise IN ( SELECT cruise_id
FROM cruise
WHERE start_harbor = 'MIAMI' );