Using the IN operator with collection data types

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 statement fragments 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.