IN Condition

The IN condition is satisfied when the expression to the left of the keyword IN is included in the list of items.

IN Condition:
 
                  (1)
|--| Expression |-------+-----+--IN----------------------------->
                        '-NOT-'
 
        .-,--------------------------------------------------.
        V                      (2)                           |
>--+-(----+-| Literal Number |-----------------------------+-+--)-+--|
   |      |                      (3)                       |      |
   |      +-| Literal DATETIME |---------------------------+      |
   |      |                   (4)                          |      |
   |      +-| Quoted String |------------------------------+      |
   |      |                      (5)                       |      |
   |      +-| Literal INTERVAL |---------------------------+      |
   |      +-USER-------------------------------------------+      |
   |      +-TODAY------------------------------------------+      |
   |      +-CURRENT--+-----------------------------------+-+      |
   |      |          |                              (6)  | |      |
   |      |          '-| DATETIME Field Qualifier |------' |      |
   |      +-SITENAME---------------------------------------+      |
   |      +-DBSERVERNAME-----------------------------------+      |
   |      |  (7)                   (6)                     |      |
   |      '--------| Literal Row |-------------------------'      |
   |  (7)                                                         |
   '------+-collection_col------------------------+---------------'
          |    .-,---------------------------.    |
          |    V                        (8)  |    |
          +-(----| Literal Collection |------+--)-+
          |                        (8)            |
          '-| Literal Collection |----------------'
 

1. See page Expression
2. See page Literal Number
3. See page Literal DATETIME
4. See page Quoted String
5. See page Literal INTERVAL
6. See page DATETIME Field Qualifier
7. Dynamic Server only
8. See page Literal Row

If you specify the NOT operator, the IN condition is TRUE when the expression is not in the list of items. NULL values do not satisfy the IN condition.

The following examples show some IN conditions:

WHERE state IN ('CA', 'WA', 'OR')
WHERE manu_code IN ('HRO', 'HSK')
WHERE user_id NOT IN (USER)
WHERE order_date NOT IN (TODAY) 

In ESQL/C, the built-in TODAY function is evaluated at execution time. The built-in CURRENT function is evaluated when a cursor opens or when the query executes, if it is a singleton SELECT statement.

The built-in USER function is case sensitive; for example, it interprets minnie and Minnie as different values.

Using the IN Operator with Collection Data Types (IDS)

You can use the IN operator to determine if an element is contained in a collection. The collection can be a simple or nested collection. (In a nested collection type, the element type of the collection is also a collection type.) When you use IN to search for an element of a collection, the expression to the left or right of the IN keyword cannot contain a BYTE or TEXT data type.

Suppose you create the following table that contains two collection columns:

CREATE TABLE tab_coll 
(
set_num SET(INT NOT NULL), 
list_name LIST(SET(CHAR(10) NOT NULL) NOT NULL)
);

The following partial examples show how you might use the IN operator for search conditions on the collection columns of the tab_coll table:

WHERE 5 IN set_num
WHERE 5.0::INT IN set_num
WHERE "5" NOT IN set_num
WHERE set_num IN ("SET{1,2,3}", "SET{7,8,9}")
WHERE "SET{'john', 'sally', 'bill'}" IN list_name
WHERE list_name IN ("LIST{""SET{'bill','usha'}"", 
                  ""SET{'ann' 'moshi'}""}",
               "LIST{""SET{'bob','ramesh'}"", 
                  ""SET{'bomani' 'ann'}""}")

In general, when you use the IN operator on a collection data type, the database server checks whether the value on the left of the IN operator is an element in the set of values on the right of the IN operator.