7.19.3 IN predicate : Hitachi Advanced Database SQL Reference

(1) Specification format

IN-predicate ::= value-expression-1 [IS] [NOT] IN {(value-expression-2[,value-expression-3]...)|table-subquery}

To Page Top

(2) Explanation of specification format

value-expression-1:

Specifies the column to be evaluated by the IN predicate. It is specified in the form of a value expression. For details about value expressions, see 7.20 Value expression.

IS:

IS can be omitted. The results will be the same regardless of whether it is specified.

NOT:

If NOT is specified, values that do not match the value expressions specified following IN will become the target of retrieval.

IN (value-expression-2[, value-expression-3]...):

The condition values are specified in the form of value expressions. If any of the specified condition values match the result of value-expression-1, the IN predicate is TRUE.

IN table-subquery:

Specifies a table subquery. For details about table subqueries, see 7.3 Subqueries.

Note that if you specify a table subquery in an IN predicate, a work table might be created. If the size of the work table DB area where the work table is created has not been estimated correctly, it might result in performance degradation. For details about estimating the size of the work table DB area, see the HADB Setup and Operation Guide. For details about work tables, see Considerations when executing an SQL statement that creates work tables in the HADB Application Development Guide.

To Page Top

(3) Evaluation of the predicate

The IN predicate is TRUE for those rows that satisfy the following condition:

If the result of value-expression-1 matches the result of any of the value expressions following IN
If the result of value-expression-1 matches any row from the result of the table subquery

If NOT is specified, the IN predicate is TRUE for those rows that satisfy the following condition:

If the result of value-expression-1 does not match the results of any of the value expressions following IN
If the result of value-expression-1 does not match any row from the result of the table subquery

To Page Top

(4) Rules

(a) Rules for specifying IN (value-expression-2 [, value-expression-3]...)

You cannot specify a dynamic parameter alone for value-expression-1.
There can be a maximum of 30,000 value expressions following IN.
Specify each value expression such that the data type of the result of value-expression-1 can be compared to the data types of the results of the value expressions following IN. For details about data types that can be compared, see (1) Data types that can be compared in 6.2.2 Data types that can be converted, assigned, and compared.

However, if you specify the predefined input representation of a date, time, or time stamp for value-expression-1, you cannot specify datetime data for value-expression-2 and subsequent value expressions. For information about predefined input representations, see 6.3.3 Predefined character-string representations.
If the result of value-expression-1 is a null value, the result of the IN predicate is undefined. Also, if the results of value expression 2 and all subsequent value expressions are null values, the result of the IN predicate is undefined.

(b) Rules for specifying IN table-subquery

If a table subquery returns zero rows (empty set), the result of the IN predicate is false. However, if NOT is specified, the result evaluates to true.
When you specify a table subquery, specify only one selection expression in the table subquery.
The IN predicate with a table subquery specified is rewritten by HADB into a quantified predicate (=ANY specification) and then processed.

To Page Top

(5) Examples

Example 1

From the sales history table (SALESLIST), this example retrieves the customer ID (USERID), product code (PUR-CODE), and date of purchase (PUR-DATE) for customers who purchased product code P001 or P003 on or after September 5, 2011.

SELECT "USERID","PUR-CODE","PUR-DATE"
    FROM "SALESLIST"
       WHERE "PUR-CODE" IN ('P001','P003')
       AND "PUR-DATE">=DATE'2011-09-05'

The underlined portion indicates the IN predicate.

Example 2

From the sales history table (SALESLIST), this example retrieves the customer ID (USERID), product code (PUR-CODE), and quantity purchased (PUR-NUM), but excludes customers whose customer ID (USERID) is U00212 or U00358.

SELECT "USERID","PUR-CODE","PUR-NUM"
    FROM "SALESLIST"
       WHERE "USERID" NOT IN ('U00212','U00358')

The underlined portion indicates the IN predicate.

Example 3

From the customer table (USERSLIST) and sales history table (SALESLIST), retrieve information on customers who purchased product code (PUR-CODE) P001.

SELECT * FROM "USERSLIST"
    WHERE "USERID" IN(SELECT "USERID" FROM "SALESLIST"
                            WHERE "PUR-CODE"='P001')

The underlined portion indicates the IN predicate.

To Page Top