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:
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 expressionFigure 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.