Omit the ITEM keyword in a collection subquery

If you omit the ITEM keyword in the collection subquery expression, the collection subquery is a MULTISET whose element type is always an unnamed ROW type. The fields of the unnamed ROW type match the data types of the expressions specified in the Projection clause of the subquery.

Suppose you create the following table that contains a column of type MULTISET:

CREATE TABLE tab2 
(
   id_num INT, 
   ms_col MULTISET(ROW(a INT) NOT NULL)
);

The following query shows how you might use a collection subquery in a WHERE clause to convert the rows of INT values that the subquery returns to a collection of type MULTISET. In this example, the database server returns rows when the ms_col column of tab2 is equal to the result of the collection subquery expression

Figure 1: Query

SELECT id_num FROM tab2 
   WHERE ms_col = (MULTISET(SELECT int_col FROM tab1));

The query omits the ITEM keyword in the collection subquery, so the INT values the subquery returns are of type MULTISET (ROW(a INT) NOT NULL) that matches the data type of the ms_col column of tab2.