7.20.3 IN predicate

(1) Specification format

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

To Page Top

(2) Explanation of specification format

{value-expression|row-value-constructor}:

Specifies what the IN predicate is being evaluated for in the form of a value expression or row value constructor to the left of the IN predicate.

The conditional value to be evaluated and compared is specified to the right of the IN predicate in the form of a value expression or row value constructor.

The meaning of the value expression or row value constructor to the left (right) of the IN predicate is shown below.

• For value expression

  In the figure above, value-expression-1 is the value expression on the left side of the IN predicate (the target of evaluation of the IN predicate), value-expression-2 and value-expression-3 are the value expressions on the right side of the IN predicate (which are the conditional values to be evaluated and compared). For details about value expressions, see 7.21 Value expression.

• For row value constructor

  In the figure above, row-value-constructor-1 is the row value constructor on the left side of the IN predicate (the target of evaluation of the IN predicate), row-value-constructor-2 and row-value-constructor-3 are the row value constructors on the right side of the IN predicate (which are the conditional values to be evaluated and compared). For details about row value constructors, see 7.18 Row value constructors.

Note the following rules:

• When specifying a row value constructor on either side of a IN predicate, two or more row value constructor elements must be specified. If only one row value constructor element is specified, the row value constructor is treated as a value expression enclosed in parentheses.

  Example of being treated as a row value constructor

  ("C1","C2") IN ((1,'A'),(2,'B'))

  Example of being treated as a value expression

  ("C1") IN ((1),(2))

  The above is the same as ""C1" IN (1,2)".

• If you specify a value expression to the left of the IN predicate, specify only a value expression or a table subquery to the right of the IN predicate.

• If a row value constructor is specified to the left of a IN predicate, only the row value constructor should be specified to the right of the IN predicate.

• Up to 30,000 value expressions or row value constructors can be specified to the right of the IN predicate.

IS:

You cannot omit the IS specification. 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 the IN predicate will become the target of retrieval.

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

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 will be 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 predicates

The conditions under which the result of the IN predicate is True are shown in the following table.

The result of an IN predicate without NOT is the same as the result of a search condition connected by an OR condition, and the result of an IN predicate with NOT is the same as the result of a search condition connected by an AND condition. The following table shows the search conditions that produce the same result as the specification of the IN predicate.

#

If you specify a table-subquery to the right of the IN predicate, read it as if each result line of that table subquery were (value-expression-2, value-expression-3,...).

The following table shows an example of a IN predicate specification and result.

To Page Top

(4) Rules

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

Retrieve the customer ID (USERID), product code (PUR-CODE), and quantity purchased (PUR-NUM) from the sales history table (SALESLIST). At this time, skip retrieval for customers whose customer IDs (USERID) are U00212 and 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.

Example 4

Rows that match the following conditions are retrieved from the sales history table (SALESLIST).

• Customer ID (USERID) is U001 and product code (PUR-CODE) is P001

• Customer ID (USERID) is U002 and product code (PUR-CODE) is P001

SELECT "USERID","PUR-CODE","PUR-NUM"
  FROM "SALESLIST"
  WHERE ("USERID","PUR-CODE") IN (('U001','P001'), ('U002','P001'))

The underlined portion is the specification of the IN predicate that specifies the row value constructors.

To Page Top